Altering Triggers

Go Up to Triggers (Data Definition Guide)

To update a trigger definition, use ALTER TRIGGER. A trigger can be altered only by its creator.

ALTER TRIGGER can change:

• Only trigger header information, 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.
• Only trigger body information, the trigger statements that follow the AS clause.
• Both trigger header and trigger body information. In this case, the new trigger definition replaces the old trigger definition.

To alter a trigger defined automatically by a CHECK constraint on a table, use ALTER TABLE to change the table definition. For more information on the ALTER TABLE statement, see Working with Tables.

Note: Direct metadata operations, such as altering triggers, increase the metadata version. At most 255 such operations can be performed before you must back up and restore the database.

The ALTER TRIGGER syntax is as follows:

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

The syntax of ALTER TRIGGER is the same as CREATE TRIGGER, except:

• The CREATE keyword is replaced by ALTER.
• FOR <table> is omitted. ALTER TRIGGER cannot be used to change the table with which the trigger is associated.
• The statement need only include parameters that are to be altered in the existing trigger, with certain exceptions listed in the following sections.

Topics

• Altering a Trigger Header
• Altering a Trigger Body