The Trigger Header
Go Up to Creating Triggers
Everything before the AS clause in the
CREATE TRIGGER statement forms the trigger header. The header must specify the name of the trigger and the name of the associated table or view. The table or view must exist before it can be referenced in
The trigger name must be unique among triggers in the database. Using the name of an existing trigger or a system-supplied constraint name results in an error.
The remaining clauses in the trigger header determine when and how the trigger fires:
- The trigger status,
INACTIVE, determines whether a trigger is activated when the specified operation occurs.
ACTIVEis the default, meaning the trigger fires when the operation occurs. Setting status to
ALTER TRIGGERis useful when developing and testing applications and triggers.
- The trigger time indicator,
AFTER, determines when the trigger fires relative to the specified operation.
BEFOREspecifies that trigger actions are performed before the operation.
AFTERspecifies that trigger actions are performed after the operation.
- The trigger statement indicator specifies the SQL operation that causes the trigger to fire:
DELETE. Exactly one indicator must be specified. To use the same trigger for more than one operation, duplicate the trigger with another name and specify a different operation.
- The optional sequence indicator,
POSITION<number>, specifies the order in which the trigger fires in relation to other triggers on the same table and event. <number> can be any integer between zero and 32,767. The default is zero. Lower-numbered triggers fire first. Multiple triggers can have the same position number; they will fire in random order.
The following example demonstrates how the
POSITION clause determines trigger firing order. Here are four headers of triggers for the
CREATE TRIGGER A FOR ACCOUNTS BEFORE UPDATE POSITION 5 AS … CREATE TRIGGER B FOR ACCOUNTS BEFORE UPDATE POSITION 0 AS … CREATE TRIGGER C FOR ACCOUNTS AFTER UPDATE POSITION 5 AS … CREATE TRIGGER D FOR ACCOUNTS AFTER UPDATE POSITION 3 AS …
When this update takes place:
UPDATE ACCOUNTS SET C = 'canceled' WHERE C2 = 5;
The following sequence of events happens: trigger B fires, A fires, the update occurs, trigger D fires, then C fires.