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.
Get Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies® now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.