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 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