George Kosmidis

Microsoft MVP | Cloud Solutions Architect | .NET Software Engineer | Organizer of Munich .NET Meetup | Speaker

SQL Server: How to simulate DROP TRIGGER IF EXISTS

by George Kosmidis / Published 9 years and 1 month ago, modified 5 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

My next events!

Online
Nürnberg Convention Center NCC Ost
Bernhard-Nocht-Straße 97, D-20359, Hamburg