The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,731 other followers

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:

List of object_types and descriptions
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).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: