Please use FireFox to view this page

create procedure sp_settriggerorder @triggername nvarchar(517), -- name of the trigger (may be 2-part) @order varchar(10), -- first, last, or none @stmttype varchar(10) -- insert, update, or delete as set nocount on declare @firstbit int -- bit for first-trigger of given @stmttype ,@lastbit int -- bit for last-trigger of given @stmttype ,@setbit int -- status bit to set (0 for clear) based on @stmttype/@order ,@trigid int -- objid of the trigger ,@tableid int -- objid of the trigger's table ,@uid smallint --user id ,@tabname nvarchar(517) -- name of the trigger's table -- VALIDATE PARAMETERS and obtain bits affected -- SELECT @order = rtrim(@order), @stmttype = rtrim(@stmttype) SELECT @firstbit = case lower(@stmttype) when 'delete' then 16384 when 'update' then 65536 when 'insert' then 262144 else NULL end SELECT @lastbit = @firstbit * 2 -- NOTE DEPENDENCY ON BIT LAYOUT HERE! SELECT @setbit = case lower(@order) when 'none' then 0 when 'first' then @firstbit when 'last' then @lastbit else NULL end IF @setbit is NULL OR @firstbit IS NULL begin raiserror(15600,-1,-1, 'sp_settriggerorder') return (1) end -- BEGIN TRAN AND LOCK SCHEMA (also checks permissions) -- BEGIN TRAN DBCC LOCKOBJECTSCHEMA(@triggername) if @@error <> 0 goto abort_exit -- VERIFY PROPER OBJECT TYPE -- select @trigid = object_id(@triggername, 'local') select @tableid = parent_obj, @uid = uid from sysobjects where id = @trigid AND xtype='TR' and ObjectProperty(@trigid,'ExecIsInsteadofTrigger')=0 and ObjectProperty(@trigid,'ExecIs'+@stmttype+'Trigger')=1 if (@tableid is NULL) begin if ObjectProperty(@trigid,'ExecIs'+@stmttype+'Trigger')=0 raiserror(15125,-1,-1, @triggername, @stmttype) else if ObjectProperty(@trigid,'ExecIsInsteadofTrigger')=1 raiserror(15133, -1, -1, @triggername) else raiserror(15126,-1,-1,@triggername) goto abort_exit end -- LOCK THE TABLE SCHEMA TOO -- select @tabname = quotename(user_name(@uid))+'.'+quotename(object_name(@tableid)) DBCC LOCKOBJECTSCHEMA(@tabname) if @@error <> 0 goto abort_exit -- VERIFY FIRST/LAST OF GIVEN TYPE DOESN'T ALREADY EXIST -- IF EXISTS (select * from sysobjects where parent_obj = @tableid AND xtype='TR' AND id <> @trigid AND ObjectProperty(id, 'ExecIs'+@order+@stmttype+'Trigger') = 1) BEGIN raiserror(15130,-1,-1,@tabname, @order, @stmttype) goto abort_exit END -- SET THE ORDER AS REQUESTED, COMMIT & RETURN SUCCESS -- update sysobjects set status = ((status&~(@firstbit|@lastbit))|@setbit) where id = @trigid commit transaction return(0) -- ROLLBACK TRAN & EXIT-FAIL -- abort_exit: rollback transaction return(1)