![CREATE TRIGGER statement [T-SQL]](gif/forward.gif)
Reference Manual
CHAPTER 9. SQL Statements
To create a new trigger in the database.
CREATE TRIGGER trigger-name trigger-time trigger-event [, trigger-event,..]
... [ 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 ) ]
... [ IF UPDATE ( column-name ) THEN
... [ { AND | OR } UPDATE ( column-name ) ] ... ]
... compound-statement
... [ ELSEIF UPDATE ( column-name ) THEN
... [ { AND | OR } UPDATE ( column-name ) ] ...
... compound-statement
... END IF ] ]
trigger-time:
BEFORE | AFTER | RESOLVE
trigger-event:
DELETE | INSERT | UPDATE | UPDATE OF column-list
Must have RESOURCE authority and have ALTER permissions on the table, or must have DBA authority. CREATE TRIGGER puts a table lock on the table, and thus requires exclusive use of the table.
Automatic commit.
Using Procedures, Triggers, and Batches
The CREATE TRIGGER statement creates a trigger associated with a table in the database, and stores the trigger in the database.
Triggers can be triggered by one or more of the following events:
INSERT Invoked whenever a new row is inserted into the table associated with the trigger.
DELETE Invoked whenever a row of the associated table is deleted.
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 has been modified.
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.
Row-level triggers can be defined to execute BEFORE or AFTER the insert, update, or delete. Statement-level triggers execute AFTER the statement. The RESOLVE trigger time is for use with SQL Remote; it fires before row-level UPDATE or UPDATE OF column-lists only.
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 enter the FOR EACH STATEMENT clause if declaring a statement-level trigger.
The WHEN (search-condition) clause can only be used with row level triggers.
Triggers of the same type (insert, update, or delete) that fire at the same time (before, after, or resolve) can use the ORDER clause to determine the order that the triggers are fired.
The REFERENCING OLD and REFERENCING NEW clauses allow you to refer to the deleted and inserted rows. For the purposes of this clause, an UPDATE is treated as a delete followed by an insert.
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.
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 WHEN clause causes the trigger to fire only for rows where the search-condition evaluates to true. The WHEN clause can be used only with row level triggers.
BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether or not the new value differs from the old value. AFTER UPDATE triggers fire only if the new value is different from the old value.
SQL/92 Persistent stored module feature. Some clauses are vendor extensions.
Sybase This syntax is different to that supported by Adaptive Server Enterprise.
When a new department head is appointed, update the manager_id column for employees in that department.
CREATE TRIGGER tr_manager BEFORE UPDATE OF dept_head_id ON department REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW BEGIN UPDATE employee SET employee.manager_id=new_dept.dept_head_id WHERE employee.dept_id=old_dept.dept_id END