Enable Trigger

ENABLE TRIGGER (Transact-SQL)

Enables a DML, DDL, or logon trigger.

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 trigger, indicates that trigger_name was created or modified to execute with server scope. ALL SERVER also applies to logon triggers.

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 Transact-SQL statements on which it was originally programmed are executed.

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

Enabling a DML trigger on a table

The following example disables trigger uAddress that was created on table Address, and then enables it.

USE AdventureWorks2008R2;

GO

DISABLE TRIGGER Person.uAddress ON Person.Address;

GO

ENABLE Trigger Person.uAddress ON Person.Address;

GO

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

Enabling all triggers that were defined with the same scope        

The following example enables all DDL triggers that were created at the server scope.

Transact-SQL

USE AdventureWorks2008R2;

GO

ENABLE Trigger ALL ON ALL SERVER;

GO