Muokkaa

Jaa

Modify or Rename DML Triggers

  • Artikkeli

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

  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 select Modify.

  4. Modify the trigger, and then select Execute.

Rename a DML trigger

  1. Delete or disable the DML trigger that you want to rename.
  2. Create the new DML trigger, specifying the new name.

Use Transact-SQL

Modify a trigger using ALTER TRIGGER

  1. Connect to the Database Engine.

  2. From the Standard bar, select New Query.

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

    1. 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

    2. 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 CREATE TRIGGER

  1. Connect to the Database Engine.

  2. From the Standard bar, select New Query.

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

Related content