Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_settriggerorder

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  sp_addsynctriggerscore (Procedure)
sp_MSgen_sync_tran_procs (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash