ENABLE TRIGGER (Transact-SQL)
Enables a DML, DDL, or logon trigger.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
Arguments
- schema_name
Is the name of the schema to which the trigger belongs. schema_name cannot 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
For a DDL or logon trigger, indicates that trigger_name was created or modified to execute with server scope.
Remarks
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 events on which it was originally programmed are executed. Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can also be disabled or enabled by using ALTER TABLE.
Permissions
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 or logon trigger with server scope (ON ALL SERVER), 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.
Examples
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 AdventureWorks;
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 and logon triggers that were created at the server scope.
USE AdventureWorks;
GO
ENABLE Trigger ALL ON ALL SERVER;
GO
See Also
Reference
DISABLE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
sys.triggers (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|