create trigger POSUPD_TRNCHRGS
on TRANSACTIONCHARGES
for update
as
	declare @trnCode	nvarchar(30)
	declare @trnDesc	nvarchar(50)
	declare @nReturn	nvarchar(50)
	declare @sTransactionid	nvarchar(30)
	
	if @@ROWCOUNT > 0
	begin
		SET NOCOUNT ON
		
		-- MOVE HERE
		
		declare c_updated cursor local for select trn_code, trn_desc from inserted
		open c_inserted
		fetch next from c_inserted into @trnCode, @trnDesc
		while @@FETCH_STATUS = 0
		begin
			-- This is the call that executes GETSESSIONVARIABLE for each row of the cursor
			-- The GETSESSIONVARIABLE stored procedure creates the global temp table based on SPID if the one does not exists
			-- I wanted to move this call out of the while loop at comment MOVE HERE
			EXECUTE @nReturn = GETSESSIONVARIABLE @sTransactionid OUTPUT
			if @@ERROR <> 0 or @nReturn <> 0
			begin
				close c_updated
				deallocate c_updated
				rollback transaction
				return
			end
			
			-- Take necessary action here...
		end
	end
GO

quote:

---

Now, since this entire thing is happening for each row in the cursor, I think it is affecting overall trigger performance

---

create procedure GETSESSIONVARIABLE 	@sPackage	nvarchar(32), 
										@sVariable	nvarchar(32), 
										@sValue		nvarchar(2000)	OUTPUT
as
	SET NOCOUNT ON
	declare @sSQL	nvarchar(500)
	declare @sTable	nvarchar(32)
	declare @nCount	int
	
	SET @sValue = N'@@Error@@'

	SET @sTable = N'##GTMP_PACKAGE_VARIABLES' + CAST( @@SPID as nvarchar(10) )

	--create temp table if table does not already exist
	if object_id('tempdb..' + @sTable ) is null
	BEGIN
		SET @sSQL = N'select * into ' + @sTable + N' from PACKAGEVARIABLES'
		EXEC (@sSQL)
	END
	
	--verify its existance
	if object_id('tempdb..' + @sTable ) is null		
		RAISERROR ('Unable to create temporary package variable table.', 16, 1 )
	
	--get the package variable value
	SET @sSQL = N'select @sValue = VARIABLE_VALUE from ' + @sTable
	SET @sSQL = @sSQL + N' where PACKAGE_NAME	= ''' + @sPackage + N''' and '
	SET @sSQL = @sSQL + N'		VARIABLE_NAME	= ''' + @sVariable + N''''
			
	EXEC sp_executesql @sSQL, N'@sValue nvarchar(2000) OUTPUT', @sValue OUTPUT
go