Modify or Rename DML Triggers
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This article describes how to modify or rename a DML trigger in SQL Server by using SQL Server Management Studio or Transact-SQL.
Limitations
When you rename a trigger, the trigger must be in the current database, and the new name must follow the rules for identifiers.
Recommendations
Avoid using 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 doesn't 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 the proposed change affects any triggers.
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:
Permissions
To alter a DML trigger requires ALTER
permission on the table or view on which the trigger is defined.
Use SQL Server Management Studio
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 select Modify.
Modify the trigger, and then select Execute.
Rename a DML trigger
- Delete or disable the DML trigger that you want to rename.
- Create the new DML trigger, specifying the new name.
Use Transact-SQL
Modify a trigger using ALTER TRIGGER
Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example 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 onINSERT
activities. This trigger is helpful because it reminds the user that updates or inserts rows into this table to also notify theCompensation
department.Create trigger.
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
Alter the trigger.
USE AdventureWorks2022; GO ALTER TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory AFTER INSERT AS RAISERROR ('Notify Compensation', 16, 10); GO
Rename a trigger using DROP TRIGGER and ALTER TRIGGER
Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example uses the DROP TRIGGER and CREATE TRIGGER statements to rename the
Sales.bonus_reminder
trigger toSales.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
Related content
- 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)