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

T-SQL ENABLE TRIGGER

Master T-SQL ENABLE TRIGGER — re-arm a previously disabled trigger. Covers single-trigger and ALL-trigger forms across DML / database / server scopes, permission requirements, the alternative ALTER TABLE form, and verification via sys.triggers.

What is ENABLE TRIGGER?

ENABLE TRIGGER turns a previously-disabled trigger back on. The trigger object itself isn't re-created — its definition has been sitting in the database all along; ENABLE just re-arms it so it fires again on the events it was created for.

This is the partner statement to DISABLE TRIGGER. Together they let you temporarily pause trigger execution — useful during bulk loads, maintenance windows, or when troubleshooting.

Syntax

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

Argument	Meaning
`trigger_name`	One or more triggers to enable, comma-separated
`ALL`	Enable 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:

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

Examples

Example 1 — Enable a Single DML Trigger

This is the most common case — turn one DML trigger back on after a maintenance window.

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

DISABLE TRIGGER trgAddressAudit ON Address;     -- pretend we disabled it earlier

T-SQL — Re-enable

ENABLE TRIGGER trgAddressAudit ON Address;
GO

OUTPUT

Messages

Commands completed successfully.

Example 2 — Enable a Database-Scope DDL Trigger

T-SQL — DDL trigger lifecycle

-- Create a "safety" trigger that 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 during a planned schema change
DISABLE TRIGGER safety ON DATABASE;
GO

-- ... do the schema work ...

-- Turn safety back on
ENABLE TRIGGER safety ON DATABASE;
GO

OUTPUT

Messages

Commands completed successfully.

Example 3 — Enable ALL Triggers in a Scope

T-SQL — Bulk enable

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

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

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

ENABLE TRIGGER vs ALTER TABLE

For DML triggers, there's a second way to enable/disable — via ALTER TABLE:

T-SQL — Alternative

ALTER TABLE Address ENABLE TRIGGER trgAddressAudit;
ALTER TABLE Address ENABLE TRIGGER ALL;

Both forms do the same thing. ENABLE TRIGGER is the more explicit, dedicated statement and works for DDL/logon triggers too — prefer it.

Checking Whether a Trigger is Enabled

T-SQL — Inspect trigger states

SELECT name,
       OBJECT_NAME(parent_id) AS parent_table,
       is_disabled
FROM   sys.triggers
ORDER BY is_disabled DESC, name;

The is_disabled column is 1 if the trigger is currently disabled, 0 if it's enabled.

Conclusion

ENABLE TRIGGER reverses a previous DISABLE TRIGGER — the trigger fires again on its events.
Specify a single trigger by name, several comma-separated names, or ALL to enable every trigger in the scope.
Match the ON clause to where the trigger lives: a table or view (DML), DATABASE (database-scope DDL), or ALL SERVER (server-scope DDL or logon).
For DML triggers, ALTER TABLE table ENABLE TRIGGER name is an equivalent alternative; ENABLE TRIGGER is preferred because it works for all trigger types.
Check the current state with sys.triggers.is_disabled.