ENABLE TRIGGER (Transact-SQL)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Enables a DML, DDL, or logon trigger.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
Enabling a trigger does not re-create it. A disabled trigger still exists as an object in the current database, but does not fire. Enabling a trigger causes it to fire when any Transact-SQL statements on which it was originally programmed are executed. Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.
To enable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.
To enable a DDL trigger with server scope (ON ALL SERVER) or a logon trigger, a user must have CONTROL SERVER permission on the server. To enable a DDL trigger with database scope (ON DATABASE), at a minimum, a user must have ALTER ANY DATABASE DDL TRIGGER permission in the current database.
A. Enabling a DML trigger on a table
The following example disables trigger uAddress that was created on table Address, and then enables it.
USE AdventureWorks2012; GO DISABLE TRIGGER Person.uAddress ON Person.Address; GO ENABLE Trigger Person.uAddress ON Person.Address; GO
B. Enabling a DDL trigger
The following example creates a DDL trigger safety with database scope, and then disables it.
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'safety') DROP TRIGGER safety ON DATABASE; GO CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK; GO DISABLE TRIGGER safety ON DATABASE; GO ENABLE TRIGGER safety ON DATABASE; GO
C. Enabling all triggers that were defined with the same scope
The following example enables all DDL triggers that were created at the server scope.
Applies to: SQL Server 2008 through SQL Server 2016.
USE AdventureWorks2012; GO ENABLE Trigger ALL ON ALL SERVER; GO