ENABLE TRIGGER (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
Enables a DML, DDL, or logon trigger.
Transact-SQL syntax conventions
ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
schema_name
Is the name of the schema to which the trigger belongs. schema_name can't be specified for DDL or logon triggers.
trigger_name
Is the name of the trigger to be enabled.
ALL
Indicates that all triggers defined at the scope of the ON clause are enabled.
object_name
Is the name of the table or view on which the DML trigger trigger_name was created to execute.
DATABASE
For a DDL trigger, indicates that trigger_name was created or modified to execute with database scope.
ALL SERVER
Applies to: SQL Server 2008 (10.0.x) and later.
For a DDL trigger, indicates that trigger_name was created or modified to execute with server scope. ALL SERVER also applies to logon triggers.
Note
This option is not available in a contained database.
Enabling a trigger doesn't re-create it. A disabled trigger still exists as an object in the current database, but doesn't fire. To enable a trigger, causes it to fire when any Transact-SQL statements on which it was originally programmed are run. Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can also be disabled or enabled by using ALTER TABLE.
To enable a DML trigger, at a minimum, a user needs 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 needs CONTROL SERVER permission on the server. To enable a DDL trigger with database scope (ON DATABASE), at a minimum, a user needs ALTER ANY DATABASE DDL TRIGGER permission in the current database.
The following example disables trigger uAddress
that was created on table Address
in the AdventureWorks database, and then enables it.
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO
ENABLE Trigger Person.uAddress ON Person.Address;
GO
The following example creates a DDL trigger safety
with database scope, and then disables and enables it.
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
The following example enables all DDL triggers that were created at the server scope.
Applies to: SQL Server 2008 (10.0.x) and later.
ENABLE Trigger ALL ON ALL SERVER;
GO
DISABLE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
sys.triggers (Transact-SQL)