SQL Server: conditionally performing a DROP TRIGGER
Posted by jpluimers on 2010/07/19
Sometimes you need to drop a trigger, and depending on your SCM, your database might not actually have it.
Many people start blinding querying the system tables for this (a few of those examples are revealed by this Google search query).
This might be caused by old Microsoft documentation showing this as an example back in SQL Server 2000.
This is not needed for DML triggers (that react on data changes), querying the system tables is only needed for DDL triggers (that react on DDL actions).
For DML triggers (the vast majority!), you can use OBJECT_ID in stead.The OBJECT_ID function has been available for a long time (I think it got introduced in SQL Server 2000, but it might have been there even longer).
In fact, the current SQL Server 2008 books on-line now explicitly mention the use of OBJECT_ID for dropping a trigger.
This is a quote from that page:
A. Dropping a DML trigger
The following example drops the employee_insupd trigger.
USE AdventureWorks2008R2; GO IF OBJECT_ID ('employee_insupd', 'TR') IS NOT NULL DROP TRIGGER employee_insupd; GO
Note 1:
Note the use of ‘TR’ as object_type parameter in the OBJECT_ID call to indicate we want to find a trigger.
The list of object_types can be obtained from the sys.objects documentation:
| object_type | description |
|---|---|
| AF | Aggregate function (CLR) |
| C | CHECK constraint |
| D | DEFAULT (constraint or stand-alone) |
| F | FOREIGN KEY constraint |
| FN | SQL scalar function |
| FS | Assembly (CLR) scalar-function |
| FT | Assembly (CLR) table-valued function |
| IF | SQL inline table-valued function |
| IT | Internal table |
| P | SQL Stored Procedure |
| PC | Assembly (CLR) stored-procedure |
| PG | Plan guide |
| PK | PRIMARY KEY constraint |
| R | Rule (old-style, stand-alone) |
| RF | Replication-filter-procedure |
| S | System base table |
| SN | Synonym |
| SQ | Service queue |
| TA | Assembly (CLR) DML trigger |
| TF | SQL table-valued-function |
| TR | SQL DML trigger |
| TT | Table type |
| U | Table (user-defined) |
| UQ | UNIQUE constraint |
| V | View |
| X | Extended stored procedure |
Note 2:
A lot of people just want to perform a DROP TRIGGER in order to change an existing trigger.
So they perform a DROP TRIGGER, directly followed by a CREATE TRIGGER.
In SQL Server however, you can perform an ALTER TRIGGER in stead.
This is much shorter, and also makes sure there isn’t a small window when there is no trigger at all.
–jeroen
via DROP TRIGGER (Transact-SQL).
The Wiert Corner - irregular stream of stuff 
Leave a Reply