Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Disables a trigger.
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.
Caution
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
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.
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.
Changing the trigger by using the ALTER TRIGGER statement enables the trigger.
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 trigger with server scope (ON ALL SERVER) or a logon trigger, 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.
The code samples in this article use the AdventureWorks2022
or AdventureWorksDW2022
sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
The following example disables trigger uAddress
that was created on table Person
.
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO
The following example creates a DDL trigger safety
with database scope, and then disables 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
The following example disables all DDL triggers that were created at the server scope.
DISABLE Trigger ALL ON ALL SERVER;
GO
ENABLE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
sys.triggers (Transact-SQL)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today