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 CREATE TRIGGER.

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, ACTIVE or INACTIVE, determines whether a trigger is activated when the specified operation occurs. ACTIVE is the default, meaning the trigger fires when the operation occurs. Setting status to INACTIVE with ALTER TRIGGER is useful when developing and testing applications and triggers.
• The trigger time indicator, BEFORE or AFTER, determines when the trigger fires relative to the specified operation. BEFORE specifies that trigger actions are performed before the operation. AFTER specifies that trigger actions are performed after the operation.
• The trigger statement indicator specifies the SQL operation that causes the trigger to fire: INSERT, UPDATE, or 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 ACCOUNTS table:

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.

Advance To:

• The Trigger Body