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