SQL Server: How to simulate DROP TRIGGER IF EXISTS

I usually store triggers, store procedures, functions, queries etc in sql files in an SVN repository. I then use an app I wrote, that opens each file and executes it. The problem of course is CREATE and ALTER. I can’t use CREATE because the same trigger might already be there. On the other hand I couldn’t use ALTER because some triggers might be new.

Since there is no DROP TRIGGER IF EXISTS, I came up with this:

USE YOUR_DATABASE;
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'YOUR_TRIGGER_NAME' AND type = 'TR')
    DROP TRIGGER  [dbo].[YOUR_TRIGGER_NAME]
GO
CREATE TRIGGER [dbo].[YOUR_TRIGGER_NAME] 
   ON  [dbo].[YOUR_TABLE] 
   AFTER UPDATE,INSERT
AS 
BEGIN
	--SQL
END;

Of course, ‘YOUR_TRIGGER_NAME’ could be ‘YOUR_FUNCTION_NAME’ or whatever! Just match it with the ‘type’. These are all the possible values of column type:

AF = Aggregate function (CLR)
C  = CHECK constraint
D  = Default or DEFAULT constraint
F  = FOREIGN KEY constraint
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued functionIF = In-lined table-function
IT = Internal table
K  = PRIMARY KEY or UNIQUE constraint
L  = Log
P  = Stored procedure
PC = Assembly (CLR) stored-procedure
R  = Rule
RF = Replication filter stored procedure
S  = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
V = View
X = Extended stored procedure

You can read more about sys.sysobjects here: http://msdn.microsoft.com/en-us/library/ms177596.aspx

Advertisements

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 )

Google+ photo

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

Connecting to %s