Edit

Delete or Disable DML Triggers

  • Article

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

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

In This Topic

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.

Using SQL Server Management Studio

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

  3. Expand Triggers, right-click the trigger to delete, and then click Delete.

  4. In the Delete Object dialog box, verify the trigger to delete, and then click OK.

To disable and enable 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 disable.

  3. Expand Triggers, right-click the trigger to disable, and then click Disable.

  4. To enable the trigger, click Enable.

Using Transact-SQL

To delete a DML 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 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 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  
  

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

To disable and enable a DML 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 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 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  
  

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

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

See Also

ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
EVENTDATA (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)