Export (0) Print
Expand All
Expand Minimize

DISABLE TRIGGER (Transact-SQL)

Updated: 12 December 2006

Disables a DML, DDL, or logon trigger.

Topic link icon Transact-SQL Syntax Conventions


DISABLE 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 cannot be specified for DDL or logon triggers.

trigger_name

Is the name of the trigger to be disabled.

ALL

Indicates that all triggers defined at the scope of the ON clause are disabled.

ms189748.Caution(en-US,SQL.90).gifCaution:
SQL Server creates triggers in databases that are published for merge replication. Specifying ALL in published databases disables these triggers, which disrupts replication. Verify that the current database is not published for merge replication before specifying ALL.

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.

Triggers are enabled by default when they are created. Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger does not fire when any Transact-SQL statements on which it was programmed are executed. Triggers can be re-enabled by using ENABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.

To disable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.

To disable a DDL or logon trigger with server scope (ON ALL SERVER), a user must have CONTROL SERVER permission on the server. To disable 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. Disabling a DML trigger on a table

The following example disables trigger uAddress that was created on table Address.

USE AdventureWorks;
GO
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO

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

C. Disabling all triggers that were defined with the same scope

The following example disables all DDL and logon triggers that were created at the server scope.

USE AdventureWorks;
GO
DISABLE Trigger ALL ON ALL SERVER;
GO

Release History

12 December 2006

New content:
  • Added information throughout the topic about logon triggers, which are introduced in SQL Server 2005 Service Pack 2.

Community Additions

ADD
Show:
© 2014 Microsoft