T-SQL DROP TRIGGER

Master T-SQL DROP TRIGGER — permanently remove triggers from the database. Covers all three syntax forms (DML, ON DATABASE, ON ALL SERVER), the IF EXISTS option, multi-drop, comparison with DISABLE TRIGGER, dropping via SSMS, and verification.

DROP TRIGGER permanently removes a trigger from the database. The trigger's definition is gone — it stops firing, its source disappears from system catalogs, and the only way to bring it back is to CREATE TRIGGER it again.

Syntax depends on the trigger's scope. There are three forms:

1. DML triggers (on a table or view)

2. DDL triggers with database scope

3. DDL triggers with server scope (and logon triggers)

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

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

DROP TRIGGER IF EXISTS trgAddressAudit;
GO

CREATE OR ALTER TRIGGER LogTableChanges
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE
AS
PRINT 'Schema change detected';
GO

-- Remove it
DROP TRIGGER IF EXISTS LogTableChanges
ON DATABASE;
GO

USE master;
GO

DROP TRIGGER IF EXISTS AuditDatabaseCreation
ON ALL SERVER;
GO

List the names comma-separated. They must all be the same scope (all DML, all database-scope DDL, or all server-scope):

-- Multiple DML triggers
DROP TRIGGER IF EXISTS trgEmployeeInserted, trgEmployeeUpdated, trgEmployeeDeleted;

-- Multiple database-scope DDL triggers
DROP TRIGGER IF EXISTS LogTableCreation, LogTableDeletion
ON DATABASE;

-- Multiple server-scope DDL or logon triggers
DROP TRIGGER IF EXISTS AuditCreate, AuditDrop
ON ALL SERVER;

SQL Server Management Studio also offers a GUI path:

1. Open Object Explorer.
2. Expand Databases → YourDatabase → Tables → YourTable → Triggers.
3. Right-click the trigger and choose Delete.
4. Click OK in the confirmation dialog.

SSMS runs the equivalent DROP TRIGGER statement for you. The script form is preferred for repeatability — you can put it in a deployment script and re-run it as needed.

After dropping, confirm the trigger is gone by querying system catalogs:

-- DML and database-scope DDL triggers
SELECT name FROM sys.triggers
WHERE  name = 'trgAddressAudit';

-- Server-scope DDL triggers
SELECT name FROM sys.server_triggers
WHERE  name = 'AuditDatabaseCreation';

An empty result confirms the trigger has been removed.

1. Always use IF EXISTS — makes scripts idempotent and safe to re-run.
2. Prefer DISABLE when you might need the trigger later. Dropping then recreating loses the trigger's edit history; disabling preserves it.
3. Save the trigger's source before dropping if there's any chance you'll want it back. sp_helptext 'trigger_name' dumps the source code; save it to a file or version control.
4. Drop triggers in dependency order. If trigger A inserts into a logging table that trigger B fires on, drop A first.
5. Coordinate with replication, change data capture, and other features that may have created triggers automatically — never drop triggers you didn't create unless you understand them.

• DROP TRIGGER permanently removes a trigger and its definition; the action is irreversible.
• Three syntax forms based on scope: DML triggers (no scope clause), ON DATABASE, or ON ALL SERVER.
• Always include IF EXISTS for safe, idempotent scripts.
• Drop multiple triggers at once with a comma-separated list — same scope only.
• For temporary suspension, use DISABLE TRIGGER instead — same effect on firing, but the definition stays.
• Confirm drops via sys.triggers / sys.server_triggers; save the source first if there's any chance you'll need it again.