Oracle PL/SQL Disable Trigger T-SQL · Triggers · Disable Trigger

T-SQL DISABLE TRIGGER

Master T-SQL DISABLE TRIGGER — temporarily pause trigger execution without dropping the trigger. Covers single and bulk-disable forms, permission requirements, the replication caveat, comparison with DROP TRIGGER, and best practices for production safety.

What is DISABLE TRIGGER?

DISABLE TRIGGER tells SQL Server to stop firing a trigger — without removing the trigger object from the database. The trigger's definition stays intact (visible in sys.triggers), but its events are ignored until you call ENABLE TRIGGER again.

Common reasons to disable temporarily:

Bulk loading data — auditing triggers can slow down a large insert by orders of magnitude.
Maintenance scripts that need to bypass policies enforced by triggers.
Troubleshooting — disable a suspect trigger to confirm it's the cause of a bug or slowdown.

⚠️ A disabled trigger doesn't run on ANY DML — including DML you intend. If you disable an audit trigger to do a bulk load, your audit log will have a gap for that period. Always re-enable triggers as soon as the maintenance work finishes.

Syntax

DISABLE TRIGGER { [ schema. ] trigger_name [, ...n] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ]

Argument	Meaning
`trigger_name`	One or more triggers to disable, comma-separated
`ALL`	Disable every trigger in the specified scope
`ON object_name`	The table or view the DML trigger lives on
`ON DATABASE`	For DDL triggers with database scope
`ON ALL SERVER`	For DDL or logon triggers with server scope

📌 Permission requirements are the same as ENABLE TRIGGER:

DML trigger: ALTER on the table or view
Database-scope DDL trigger: ALTER ANY DATABASE DDL TRIGGER
Server-scope DDL trigger or logon trigger: CONTROL SERVER

Examples

Example 1 — Disable a Single DML Trigger

SQL — Setup

CREATE TABLE Address (
    AddressID  INT PRIMARY KEY,
    Street     NVARCHAR(100),
    City       NVARCHAR(50)
);

CREATE OR ALTER TRIGGER trgAddressAudit
ON Address
AFTER UPDATE
AS
PRINT 'Address row updated';
GO

T-SQL — Disable, do work, re-enable

DISABLE TRIGGER trgAddressAudit ON Address;
GO

-- ... bulk update or other work where the audit trigger isn't wanted ...

ENABLE TRIGGER trgAddressAudit ON Address;
GO

OUTPUT

Messages

Commands completed successfully.

Example 2 — Disable a Database-Scope DDL Trigger

T-SQL — Pause a "safety" DDL trigger

-- Create a trigger that normally blocks DROP/ALTER TABLE
CREATE OR ALTER TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
    PRINT 'You must disable trigger ''safety'' to drop or alter tables!';
    ROLLBACK;
END;
GO

-- Disable it before a planned schema change
DISABLE TRIGGER safety ON DATABASE;
GO

-- ... now CREATE / ALTER / DROP TABLE statements work normally ...

Example 3 — Disable ALL Triggers in a Scope

T-SQL — Bulk disable

-- All DML triggers on a specific table
DISABLE TRIGGER ALL ON Address;

-- All database-scope DDL triggers in the current database
DISABLE TRIGGER ALL ON DATABASE;

-- All server-scope DDL and logon triggers
DISABLE TRIGGER ALL ON ALL SERVER;

⚠️ Be very careful with DISABLE TRIGGER ALL ON DATABASE on a production system. If you have audit triggers, integrity-enforcement triggers, or business-rule triggers, disabling them all even briefly can let bad data in or skip required logging. Disable specific triggers, not all of them, whenever possible.

Replication Caveat

If your database is part of merge replication, disabling triggers can disrupt the replication process — many internal mechanisms in replication rely on triggers Microsoft created automatically. Always check the replication setup before issuing a broad DISABLE TRIGGER ALL in a published or subscribing database.

DISABLE TRIGGER vs DROP TRIGGER

Aspect	`DISABLE TRIGGER`	`DROP TRIGGER`
Effect	Trigger stops firing	Trigger is removed entirely
Definition retained?	Yes — still visible in `sys.triggers`	No — gone, source code lost
Reversible?	Yes — `ENABLE TRIGGER`	No — must `CREATE` from scratch
Use when	Temporarily pausing a trigger	Permanently removing a trigger

Best Practices

Disable the smallest scope possible. Disable a single trigger, not ALL, when you can.
Re-enable as soon as you're done. Wrap the disable/work/enable sequence in a single script so the disabled state can never persist accidentally.
Audit trigger states regularly. Run a check (SELECT name FROM sys.triggers WHERE is_disabled = 1) to catch triggers that were left off by mistake.
Document why you're disabling. A comment in the maintenance script saves the next person from wondering whether the disabled state is intentional.

Conclusion

DISABLE TRIGGER stops a trigger from firing without removing its definition; reverse with ENABLE TRIGGER.
Same syntax shape as ENABLE: name a trigger or use ALL; specify scope with ON object, ON DATABASE, or ON ALL SERVER.
Use it for short maintenance windows — bulk loads, schema-change scripts, troubleshooting.
Always re-enable promptly; don't leave triggers off in production for longer than the work requires.
Avoid DISABLE TRIGGER ALL in production unless you understand every trigger that's about to go silent.