June 8, 2012

sp_settriggerorder (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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

Total Pageviews