SQL Server DROP TRIGGER

Summary: in this tutorial, you will learn how to use the SQL Server DROP TRIGGER statement to remove existing triggers.

Introduction SQL Server DROP TRIGGER statements

The SQL Server DROP TRIGGER statement drops one or more triggers from the database. The following illustrates the syntax of the DROP TRIGGER statement that removes DML triggers:

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• IF EXISTS conditionally removes the trigger only when it already exists.
• schema_name is the name of the schema to which the DML trigger belongs.
• trigger_name is the name of the trigger that you wish to remove.

If you want to remove multiple triggers at once, you need to separate triggers by commas.

To remove one or more DDL triggers, you use the following form of the DROP TRIGGER statement:

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON { DATABASE | ALL SERVER };
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• DATABASE indicates that the scope of the DDL trigger applies to the current database.
• ALL SERVER indicates the scope of the DDL trigger applies to the current server.

To remove a LOGON event trigger, you use the following syntax:

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON ALL SERVER;
Code language: SQL (Structured Query Language) (sql)

Notice that when you drop a table, all triggers associated with the table are also removed automatically.

SQL Server DROP TRIGGER examples

A) SQL Server DROP TRIGGER – drop a DML trigger example

The following statement drops a DML trigger named sales.trg_member_insert:

DROP TRIGGER IF EXISTS sales.trg_member_insert;
Code language: SQL (Structured Query Language) (sql)

B) SQL Server DROP TRIGGER – drop a DDL trigger example

The following statement removes the trg_index_changes trigger:

DROP TRIGGER IF EXISTS trg_index_changes;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use remove a trigger using the DROP TRIGGER statement.