Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

IV.3.5. Maintaining Triggers

During the life of a trigger, you'll likely need to make some changes.

For example, you might have originally created a trigger that provided feedback to a user stating that tables shouldn't be modified in a database. Instead of just informing users that it shouldn't be done, you decide that you want to audit whenever it's done by logging the change into the database.

With this situation, you don't have to re-create the trigger from scratch. Instead, you can use the ALTER statement to make the change.

The following basic syntax modifies a DML trigger:

ALTER TRIGGER triggername
ON table or view
AFTER or INSTEAD OF
INSERT or UPDATE or DELETE
AS
trigger code

This basic syntax modifies a DDL trigger:

ALTER TRIGGER triggername
ON DATABASE or SERVER
FOR event
AS
trigger code

The syntax is exactly the same except that the CREATE keyword is replaced with the ALTER keyword.