Modify or Rename DML Triggers
This topic describes how to modify or rename a DML trigger in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.
In This Topic
Limitations and Restrictions
When you rename a trigger, the trigger must be in the current database, and the new name must follow the rules for identifiers.
We recommend you do not use the sp_rename stored procedure to rename a trigger. Changing any part of an object name can break scripts and stored procedures. Renaming a trigger does not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. We recommend that you drop and and re-create the trigger instead.
If you change the name of an object referenced by a DML trigger, you must modify the trigger so that its text reflects the new name. Therefore, before you rename an object, display the dependencies of the object first to determine whether any triggers are affected by the proposed change.
A DML trigger can also be modified to encrypt its definition.
To view the dependencies of a trigger, you can use SQL Server Management Studio or the following function and catalog views:
To alter a DML trigger requires ALTER permission on the table or view on which the trigger is defined.
To modify a DML trigger
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to modify.
Expand Triggers, right-click the trigger to modify, and then click Modify.
Modify the trigger, and then click Execute.
To rename a DML trigger
To modify a trigger using ALTER TRIGGER
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following examples into the query. Execute the first example to create a DML trigger that prints a user-defined message to the client when a user tries to add or change data in the SalesPersonQuotaHistory table. Execute the ALTER TRIGGER statement to modify the trigger to fire only on INSERT activities. This trigger is helpful because it reminds the user that updates or inserts rows into this table to also notify the Compensation department.
USE AdventureWorks2012; GO IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL DROP TRIGGER Sales.bonus_reminder; GO CREATE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory WITH ENCRYPTION AFTER INSERT, UPDATE AS RAISERROR ('Notify Compensation', 16, 10); GO
USE AdventureWorks2012; GO ALTER TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory AFTER INSERT AS RAISERROR ('Notify Compensation', 16, 10); GO
To rename a trigger using DROP TRIGGER and ALTER TRIGGER
USE AdventureWorks2012; GO IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL DROP TRIGGER Sales.bonus_reminder; GO CREATE TRIGGER Sales.bonus_reminder_2 ON Sales.SalesPersonQuotaHistory WITH ENCRYPTION AFTER INSERT, UPDATE AS RAISERROR ('Notify Compensation', 16, 10); GO