George Kosmidis

Microsoft MVP | Speaks of Azure, AI & .NET | Founder of Munich .NET
Building tomorrow @
slalom
slalom

SQL Server: How to simulate DROP TRIGGER IF EXISTS

by George Kosmidis / Published 11 years ago, modified 2 years and 3 months ago

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?WT.mc_id=DT-MVP-5004591

This page is open source. Noticed a typo? Or something unclear?
Edit Page Create Issue Discuss
Microsoft MVP - George Kosmidis
Azure Architecture Icons - SVGs, PNGs and draw.io libraries