CREATE TRIGGER statement

Use this statement to create a trigger on a table.

Syntax

CREATE [ OR REPLACE ] TRIGGER trigger-name trigger-type
{ trigger-event-list | UPDATE OF column-list }
[ ORDER integer ] ON table-name
[ REFERENCING [ OLD AS old-name ]
   [ NEW AS new-name ] 
   [ REMOTE AS remote-name ] ]
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN ( search-condition ) ]
trigger-body

column-list :  column-name[, ...]

trigger-type : 
BEFORE 
| AFTER 
| INSTEAD OF 
| RESOLVE

trigger-event-list : trigger-event[, ...  ]

trigger-event : 
DELETE 
| INSERT 
| UPDATE

trigger-body : a BEGIN statement. See BEGIN statement.

Parameters

OR REPLACE clause Specifying OR REPLACE (CREATE OR REPLACE TRIGGER) creates a new trigger, or replaces an existing trigger with the same name.
trigger-event Triggers can be fired by the following events. You can define either multiple triggers for DELETE, INSERT, or UPDATE events, or one trigger for an UPDATE OF column-list event:
- DELETE Invoked whenever a row of the associated table is deleted.
- INSERT Invoked whenever a new row is inserted into the table associated with the trigger.
- UPDATE Invoked whenever a row of the associated table is updated.
- UPDATE OF column-list Invoked whenever a row of the associated table is updated and a column in the column-list is modified. This type of trigger event cannot be used in a trigger-event-list; it must be the only trigger event defined for the trigger. This clause cannot be used in an INSTEAD OF trigger.
  You can write separate triggers for each event that you need to handle or, if you have some shared actions and some actions that depend on the event, you can create a trigger for all events and use an IF statement to distinguish the action taking place. For more information about trigger operations, see Trigger operation conditions.
trigger-type Row-level triggers can be defined to execute BEFORE, AFTER, or INSTEAD OF an insert, update, or delete operation. Statement-level triggers can be defined to execute INSTEAD OF or AFTER the statement.
BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether the new value differs from the old value. That is, if a column-list is specified for a BEFORE UPDATE trigger, the trigger fires if any of the columns in column-list appear in the SET clause of the UPDATE statement. If a column-list is specified for an AFTER UPDATE trigger, the trigger is fired only if the value of any of the columns in column-list is changed by the UPDATE statement.

INSTEAD OF triggers are the only form of trigger that you can define on a regular view. INSTEAD OF triggers replace the triggering action with another action. When an INSTEAD OF trigger fires, the triggering action is skipped and the specified action is performed. INSTEAD OF triggers can be defined at a row-level or a statement-level. A statement-level INSTEAD OF trigger replaces the entire statement, including all row-level operations. If a statement-level INSTEAD OF trigger fires, no row-level triggers fire as a result of that statement. However, the body of the statement-level trigger could perform other operations that, in turn, cause other row-level triggers to fire.

If you are defining an INSTEAD OF trigger, you cannot use the UPDATE OF column-list clause, the ORDER clause, or the WHEN clause.

For more information about the capabilities of, and restrictions for, INSTEAD OF triggers, see INSTEAD OF triggers.

The RESOLVE trigger type is for use with SQL Remote: it fires before row-level UPDATE or UPDATE OF column-list only.
FOR EACH clause To declare a trigger as a row-level trigger, use the FOR EACH ROW clause. To declare a trigger as a statement-level trigger, you can either use a FOR EACH STATEMENT clause or omit the FOR EACH clause. For clarity, it is recommended that you specify the FOR EACH STATEMENT clause if you are declaring a statement-level trigger.
ORDER clause When defining additional triggers of the same type (insert, update, or delete) to fire at the same time (before, after, or resolve), you must specify an ORDER clause to tell the database server the order in which to fire the triggers. Order numbers must be unique among same-type triggers configured to fire at the same time. If you specify an order number that is not unique, an error is returned. Order numbers do not need to be in consecutive order (for example, you could specify 1, 12, 30). The database server fires the triggers starting with the lowest number.
If you omit the ORDER clause, or specify 0, the database server assigns the order of 1. However, if another same-type trigger is already set to 1, an error is returned.

When adding additional triggers, you may need to modify the existing same-type triggers for the event, depending on whether the actions of the triggers interact. If they do not interact, the new trigger must have an ORDER value higher than the existing triggers. If they do interact, you need to consider what the other triggers do, and you may need to change the order in which they fire.

The ORDER clause is not supported for INSTEAD OF triggers since there can only be one INSTEAD OF trigger of each type (insert, update, or delete) defined on a table or view.
REFERENCING clause The REFERENCING OLD and REFERENCING NEW clauses allow you to refer to the inserted, deleted, or updated rows. For the purposes of this clause, an UPDATE is treated as a delete followed by an insert.
An INSERT takes the REFERENCING NEW clause, which represents the inserted row. There is no REFERENCING OLD clause.

A DELETE takes the REFERENCING OLD clause, which represents the deleted row. There is no REFERENCING NEW clause.

An UPDATE takes the REFERENCING OLD clause, which represents the row before the update, and it takes the REFERENCING NEW clause, which represents the row after the update.

The meaning of REFERENCING OLD and REFERENCING NEW differs, depending on whether the trigger is a row-level or a statement-level trigger. For row-level triggers, the REFERENCING OLD clause allows you to refer to the values in a row prior to an update or delete, and the REFERENCING NEW clause allows you to refer to the inserted or updated values. The OLD and NEW rows can be referenced in BEFORE and AFTER triggers. The REFERENCING NEW clause allows you to modify the new row in a BEFORE trigger before the insert or update operation takes place.

For statement-level triggers, the REFERENCING OLD and REFERENCING NEW clauses refer to declared temporary tables holding the old and new values of the rows. The default names for these tables are deleted and inserted.

The REFERENCING REMOTE clause is for use with SQL Remote. It allows you to refer to the values in the VERIFY clause of an UPDATE statement. It should be used only with RESOLVE UPDATE or RESOLVE UPDATE OF column-list triggers.
WHEN clause The trigger fires only for rows where the search-condition evaluates to true. The WHEN clause can be used only with row level triggers. This clause cannot be used in an INSTEAD OF trigger.
trigger-body The trigger body contains the actions to take when the triggering action occurs, and consists of a BEGIN statement. See BEGIN statement.
You can include trigger operation conditions in the BEGIN statement. Trigger operation conditions carry out actions depending on the trigger event that caused the trigger to fire. For example, if the trigger is defined to fire for both updates and deletes, you can specify different actions for the two conditions. For more information about trigger operation conditions, including an example, see Trigger operation conditions.

Remarks

The CREATE TRIGGER statement creates a trigger associated with a table in the database, and stores the trigger in the database.

You cannot define a trigger on a materialized view. If you do, a SQLE_INVALID_TRIGGER_MATVIEW error is returned.

The trigger is declared as either a row-level trigger, in which case it executes before or after each row is modified, or as a statement-level trigger, in which case it executes after the entire triggering statement is completed.

Permissions

Must have RESOURCE authority and have ALTER permissions on the table, or must be the owner of the table or have DBA authority. CREATE TRIGGER puts a table lock on the table, and requires exclusive use of the table.

Side effects

Automatic commit.

Standards and compatibility

SQL/2003 Persistent Stored Module feature. Some clauses are vendor extensions.

Example

This example creates a statement-level trigger. You must first create a table:

CREATE TABLE t0
( id integer NOT NULL,
 times timestamp NULL DEFAULT current timestamp,
 remarks text NULL,
 PRIMARY KEY ( id )
);

Next, create a statement-level trigger for this table:

CREATE TRIGGER myTrig AFTER INSERT ORDER 4 ON t0
REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
  DECLARE @id1 INTEGER;
  DECLARE @times1 TIMESTAMP;
  DECLARE @remarks1 LONG VARCHAR;
  DECLARE @err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
//declare a cursor for table new_name
  DECLARE new1 CURSOR FOR
   SELECT id, times, remarks FROM new_name;
  OPEN new1;
 //Open the cursor, and get the value
  LoopGetRow:
  LOOP
      FETCH NEXT new1 INTO @id1, @times1,@remarks1;
      IF SQLSTATE = @err_notfound THEN
   LEAVE LoopGetRow
      END IF;
      //print the value or for other use
      PRINT (@remarks1);
  END LOOP LoopGetRow;
  CLOSE new1
END;

The following example replaces the myTrig trigger created in the previous example.

CREATE OR REPLACE TRIGGER myTrig AFTER INSERT ORDER 4 ON t0
REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
FOR L1 AS new1 CURSOR FOR
   SELECT id, times, remarks FROM new_name;
DO
     //print the value or for other use
     PRINT (@remarks1);
END FOR;

The next example shows how you can use REFERENCING NEW in a BEFORE UPDATE trigger. This example ensures that postal codes in the new Employees table are in uppercase:

CREATE TRIGGER emp_upper_postal_code
BEFORE UPDATE OF PostalCode
ON Employees
REFERENCING NEW AS new_emp
FOR EACH ROW
WHEN ( ISNUMERIC( new_emp.PostalCode ) = 0 )
BEGIN
   -- Ensure postal code is uppercase (employee might be 
   -- in Canada where postal codes contain letters)
   SET new_emp.PostalCode = UPPER(new_emp.PostalCode)
END;

UPDATE Employees SET state='ON', PostalCode='n2x 4y7' WHERE EmployeeID=191;
SELECT PostalCode FROM Employees WHERE EmployeeID = 191;

The next example shows how you can use REFERENCING OLD in a BEFORE DELETE trigger. This example prevents deleting an employee from the Employees table who has not been terminated.

CREATE TRIGGER TR_check_delete_employee 
BEFORE DELETE
ON Employees
REFERENCING OLD AS current_employees
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
   IF current_employees.TerminationDate IS NULL THEN
    RAISERROR 30001 'You cannot delete an employee who has not been fired';
    END IF;
END;

The next example shows how you can use REFERENCING NEW and REFERENCING OLD in a BEFORE UPDATE trigger. This example prevents a decrease in an employee's salary.

CREATE TRIGGER TR_check_salary_decrease 
    BEFORE UPDATE
      ON Employees
    REFERENCING OLD AS before_update 
    NEW AS after_update 
FOR EACH ROW 
BEGIN
   IF after_update.salary < before_update.salary THEN
    RAISERROR 30002 'You cannot decrease a salary';
    END IF;
END;

The next example shows how you can use REFERENCING NEW and REFERENCING OLD in a BEFORE UPDATE trigger. This example also disallows decreasing an employee's salary, but this trigger is more efficient because it fires only when the salary column is updated.

CREATE TRIGGER TR_check_salary_decrease_column 
    BEFORE UPDATE OF Salary
      ON Employees
    REFERENCING OLD AS before_update 
    NEW AS after_update
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
   IF after_update.salary < before_update.salary THEN
    RAISERROR 30002 'You cannot decrease a salary';
End IF;
END;

The next example shows how you can use REFERENCING NEW and in a BEFORE INSERT and UPDATE trigger. The following example creates a trigger that will fire before a row in the SalesOrderItems table is inserted or updated.

CREATE TRIGGER TR_update_date 
   BEFORE INSERT, UPDATE
     ON SalesOrderItems
   REFERENCING NEW AS new_row
FOR EACH ROW 
BEGIN
   SET new_row.ShipDate = CURRENT TIMESTAMP;
END;

Discuss this page in DocCommentXchange.
Send feedback about this page using email.