Modify or Rename DML Triggers

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This topic describes how to modify or rename a DML trigger in SQL Server by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before You Begin

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.

Recommendations

  • 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 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:

Security

Permissions

To alter a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

Using SQL Server Management Studio

To modify a DML trigger

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to modify.

  3. Expand Triggers, right-click the trigger to modify, and then click Modify.

  4. Modify the trigger, and then click Execute.

To rename a DML trigger

  1. Delete the trigger that you want to rename.

  2. Re-create the trigger, specifying the new name.

Using Transact-SQL

To modify a trigger using ALTER TRIGGER

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. 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 AdventureWorks2022;  
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 AdventureWorks2022;  
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

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example use the DROP TRIGGER and ALTER TRIGGER statements to rename the Sales.bonus_reminder trigger to Sales.bonus_reminder_2.

USE AdventureWorks2022;  
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  
  

See Also

CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_rename (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
Get Information About DML Triggers
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)