The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_settriggerorder(nvarchar @triggername, varchar @order
, varchar @stmttype
, varchar @namespace)
MetaData:
create procedure sys.sp_settriggerorder @triggername nvarchar(517), -- name of the trigger (may be 2-part) @order varchar(10), -- first, last, or none @stmttype varchar(50), -- statement (event) type @namespace varchar(10) = NULL -- database, server or null as set nocount on declare @stm_ty int -- statement type enumeration value for DML triggers ,@ns int -- namespace enumeration value ,@order_val int -- order enumeration value ,@trigid int -- objid of the trigger ,@tableid int -- objid of the trigger's table ,@trigObjName nvarchar(517) -- object name part of the trigger name ,@trigSchemaName nvarchar(517) -- schema name part of the trigger name ,@orderIn varchar(10) ,@stmttypeIn varchar(50) set @trigObjName = null set @trigSchemaName = null select @orderIn = @order ,@stmttypeIn = @stmttype -- VALIDATE PARAMETERS and obtain bits affected -- select @order = rtrim(LOWER (@order collate Latin1_General_CI_AS)) ,@stmttype = rtrim(LOWER (@stmttype collate Latin1_General_CI_AS)) if @@error <> 0 select @trigid = null -- ============================================================ -- DEVNOTE: -- x_eonc_TrgOnServer = 20, // Namespace for Triggers on the Server -- x_eonc_TrgOnDatabase = 21, // Namespace for Triggers on Databases -- x_eonc_Standard = 0 // Standard Namespace -- ============================================================ if not (@namespace is NULL) begin select @ns = (case lower(@namespace) when 'server' then 20 when 'database' then 21 end) end else begin set @ns = 0 -- null means standard namespace end select @order_val = (case @order when 'none' then 0 when 'first' then -1 when 'last' then 1 end) if @order_val is null OR @ns is null OR @stmttype is null begin raiserror(15600,-1,-1, 'sys.sp_settriggerorder') return (1) end -- BEGIN TRANSACTION AND LOCK SCHEMA (also checks permissions) -- BEGIN TRANSACTION -- ============================================================ -- DEVNOTE: -- x_eonc_TrgOnServer = 20, // Namespace for Triggers on the Server -- x_eonc_TrgOnDatabase = 21, // Namespace for Triggers on Databases -- ============================================================ -- parse the trigger name for server level and db level triggers if @ns = 20 or @ns = 21 begin Select @trigObjName = parsename(@triggername, 1) Select @trigSchemaName = parsename(@triggername, 2) if NOT (@trigSchemaName is null) begin -- schema name specified for extended trigger. Error out raiserror(1094,-1,-1) goto abort_exit end end -- ============================================================ -- DEVNOTE: -- x_eonc_TrgOnServer = 20, // Namespace for Triggers on the Server -- x_eonc_TrgOnDatabase = 21, // Namespace for Triggers on Databases -- ============================================================ -- use master db for server level trigger if @ns = 20 begin select @trigid = object_id from sys.server_triggers where name = @trigObjName end else if @ns = 21 begin select @trigid = object_id from sys.triggers where name = @trigObjName and parent_class = 0 end else begin select @trigid = object_id(@triggername, 'local') end -- Validate the statement type for DML triggers. if @ns = 0 begin select @stm_ty = (case @stmttype when 'delete' then 0 when 'update' then 1 when 'insert' then 2 end) if (@stm_ty is null) begin raiserror(15600,-1,-1, 'sys.sp_settriggerorder') goto abort_exit end end -- VERIFY PROPER OBJECT TYPE -- if not (@trigid is null) begin -- schema lock trigger if @ns = 0 begin EXEC %%Object(MultiName = @triggername).LockMatchID(ID = @trigid, Exclusive = 1, BindInternal = 0) if @@error <> 0 select @trigid = null else select @tableid = parent_object_id from sys.objects where object_id = @trigid and (type = 'TR' or type = 'TA') and ObjectProperty(@trigid,'ExecIsInsteadofTrigger')=0 and ObjectProperty(@trigid,'ExecIs'+@stmttype+'Trigger')=1 end else begin EXEC %%TriggerEx(MultiName = @trigObjName).ValidateExtendedTriggerTypeAndOrder( ID = @trigid, NameSpaceClass = @ns, StatementType = @stmttype, TriggerOrder = @order_val) end end if (@trigid is null) begin raiserror(15165,-1,-1,@triggername) goto abort_exit end if @ns = 0 begin 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(15165,-1,-1,@triggername) goto abort_exit end -- VERIFY FIRST/LAST OF GIVEN TYPE DOESN'T ALREADY EXIST -- if exists (select * from sys.objects where parent_object_id = @tableid and (type='TR' or type='TA') and object_id <> @trigid and ObjectProperty(object_id, 'ExecIs'+@order+@stmttype+'Trigger') = 1) begin raiserror(15130,-1,-1, @orderIn, @stmttypeIn) goto abort_exit end end -- TABLE SCHEMA ALREADY LOCKED VIA LOCKING TRIGGER -- -- set THE ORDER as REQUESTED, COMMIT & return SUCCESS -- EXEC %%TriggerEx(ID = @trigid).SetTriggerOrder(NameSpaceClass = @ns, StatementType = @stmttype, TriggerOrder = @order_val, Value = @triggername, Value = @order, Value = @stmttype, Value = @namespace) COMMIT TRANSACTION return(0) -- EXIT-FAIL -- abort_exit: COMMIT TRANSACTION return(1) -- sp_settriggerorder
No comments:
Post a Comment