ALTER TRIGGER

Go Up to Statement and Function Reference (Language Reference Guide)

Changes an existing trigger. Available in DSQL and isql.

 ALTER TRIGGER <name> [ACTIVE | INACTIVE]
 [{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
 [POSITION <number>]
 [AS trigger_body] ;

Argument	Description
<name>	Name of an existing trigger.
ACTIVE	[Default] Specifies that a trigger action takes effect when fired.
INACTIVE	Specifies that a trigger action does not take effect.
BEFORE	Specifies the trigger fires before the associated operation takes place.
AFTER	Specifies the trigger fires after the associated operation takes place.
DELETE|INSERT|UPDATE	Specifies the table operation that causes the trigger to fire.
POSITION <number>	Specifies order of firing for triggers before the same action or after the same action. <number> must be an integer between 0 and 32,767, inclusive. Lower-number triggers fire first. Triggers for a table need not be consecutive; triggers on the same action with the same position number fire in random order.
<trigger_body>	Body of the trigger: a block of statements in procedure and trigger language. See CREATE TRIGGER. for a complete description.

Description: ALTER TRIGGER changes the definition of an existing trigger. If any of the arguments to ALTER TRIGGER are omitted, then they default to their current values, that is the value specified by CREATE TRIGGER, or the last ALTER TRIGGER.

ALTER TRIGGER can change:

• Header information only, including the trigger activation status, when it performs its actions, the event that fires the trigger, and the order in which the trigger fires compared to other triggers.
• Body information only, the trigger statements that follow the AS clause.
• Header and trigger body information. In this case, the new trigger definition replaces the old trigger definition.

A trigger can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

Note: To alter a trigger defined automatically by a CHECK constraint on a table, use ALTER TABLE to change the constraint definition.

Examples: The following statement modifies the trigger, SET_CUST_NO, to be inactive:

ALTER TRIGGER SET_CUST_NO INACTIVE;

The next statement modifies the trigger, SET_CUST_NO, to insert a row into the table, NEW_CUSTOMERS, for each new customer.

ALTER TRIGGER SET_CUST_NO FOR CUSTOMER
BEFORE INSERT AS
BEGIN
NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
INSERT INTO NEW_CUSTOMERS(NEW.CUST_NO, TODAY)
END ;

See Also

• CREATE TRIGGER
• DROP TRIGGER
• Procedures and Triggers
• Data Definition Guide