Modify or Rename DML Triggers

Artykuł
02/13/2015

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

Before you begin:

Limitations and Restrictions

Recommendations

Security
To modify or rename a DML trigger, using:

SQL Server Management Studio

Transact-SQL

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

Ikona strzałki używana z łączem Powrót na górę strony [Top]

Using SQL Server Management Studio

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

Delete the trigger that you want to rename.
Re-create the trigger, specifying the new name.

Ikona strzałki używana z łączem Powrót na górę strony [Top]

Using Transact-SQL

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

Connect to the Database Engine.
From the Standard bar, click New Query.
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 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

Ikona strzałki używana z łączem Powrót na górę strony [Top]