Delete or Disable DML Triggers

Artykuł
02/13/2015

This topic describes how to delete or disable a DML trigger in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before you begin:

Recommendations

Security
To delete or disable a DML trigger, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

Recommendations

When a trigger is deleted, it is dropped from the current database. The table and the data upon which it is based are not affected. Deleting a table automatically deletes any triggers on the table.
A trigger is enabled by default when it is created.
Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger will not fire when any INSERT, UPDATE, or DELETE statement on which it was programmed is executed. Triggers that are disabled can be reenabled. Enabling a trigger does not re-create it. The trigger fires in the same manner as when it was originally created.

Security

Permissions

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

To disable or enable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.

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

Using SQL Server Management Studio

To delete 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 delete.
Expand Triggers, right-click the trigger to delete, and then click Delete.
In the Delete Object dialog box, verify the trigger to delete, and then click OK.

To disable and enable 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 disable.
Expand Triggers, right-click the trigger to disable, and then click Disable.
To enable the trigger, click Enable.

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

Using Transact-SQL

To delete a DML trigger

Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following examples into the query window. Execute the CREATE TRIGGER statement to create the Sales.bonus_reminder trigger. To delete the trigger, execute the DROP TRIGGER statement.

--Create the 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
ON Sales.SalesPersonQuotaHistory
WITH ENCRYPTION
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Compensation', 16, 10);
GO

--Delete the trigger.
USE AdventureWorks2012;
GO
IF OBJECT_ID ('Sales.bonus_reminder', 'TR') IS NOT NULL
   DROP TRIGGER Sales.bonus_reminder;
GO

To disable and enable a DML trigger

Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following examples into the query window. Execute the CREATE TRIGGER statement to create the Sales.bonus_reminder trigger. To disable and enable the trigger, execute the DISABLE TRIGGER and ENABLE TRIGGER statements, respectively.

--Create the 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
ON Sales.SalesPersonQuotaHistory
WITH ENCRYPTION
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Compensation', 16, 10);
GO

--Disable the trigger.
USE AdventureWorks2012;
GO
DISABLE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory;
GO

--Enable the trigger.
USE AdventureWorks2012;
GO
ENABLE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory;
GO

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