SQL Server: How to simulate DROP TRIGGER IF EXISTS

0 0 votes
Article Rating

I usually store triggers, store procedures, functions, queries etc in sql files in an SVN repository. I then use a simple app I wrote, that just 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:

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

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments