SQL Server – Trigger – Metadata

Background

Need to document our triggers.

SQL

Code


; with cteTriggerEvent
(
      [object_id]
    , [type]
)
as
(

    select
            [object_id]
                = tblST.object_id

        , [type]
            =
                stuff
                (
                    (

                        select 
                                ','
                                + tblSTE_Inner.[type_desc]

                        from sys.trigger_events tblSTE_Inner

                        where  tblST.[object_id]
                                    = tblSTE_Inner.[object_id]

                        for xml path('')
                    )
                    , 1
                    , 1
                    , ''

                )


    from sys.triggers tblST

)

select 

        [object] = 
                        object_schema_name(tblST.parent_id)
                    + '.'
                    + object_name(tblST.parent_id)
    , [trigger] 
        = tblST.[name]
        
    , [disabled]
        
        = case tblST.[is_disabled]
                when 1 then 'Yes'
                else 'No'
            end

    , [isInsteadofTrigger]
        = case tblST.is_instead_of_trigger
                when 1 then 'Yes'
                else 'No'
            end
                
    , tblSTE.[type]

from   sys.triggers tblST

inner join cteTriggerEvent tblSTE

        on tblST.[object_id] = tblSTE.[object_id]

order by
          [disabled]
        , [object]
        , [trigger]

Output

Output – msdb

 

Source Control

GitHub

DanielAdeniji/SQLServerTriggerMetadata
Link

Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Introduction

So basically we have a SQL Server Database Application and our topology involves a few DB SQL Server Instances.  During development, we are trying to constrain each Development\Developer environment to a single (lone) server.

Processing

Processing – Linked Server – Loopback (Create)



use master
go

/*
	select * from sys.servers
*/

declare @serverNameLocal sysname
declare @lsRemote        sysname
declare @tab		 sysname

set @lsRemote = 'LSREMOTE'
set @serverNameLocal = cast(serverproperty('servername') as sysname)
set @tab = char(9)

if exists
	(
	   select *
	   from   sys.servers tblServer
	   where  tblServer.name = @lsRemote
	)
begin

	print @tab + 'Dropping server ' + @lsRemote + ' ....'

	EXEC master.dbo.sp_dropserver @server=@lsRemote, @droplogins='droplogins'

end

print  @tab + 'Adding Self Server as Linked Server ... ' + @lsRemote

EXEC master.dbo.sp_addlinkedserver 
		  @server = @lsRemote
		, @srvproduct=@serverNameLocal
		, @provider=N'SQLOLEDB'
		, @datasrc=@serverNameLocal

EXEC master.dbo.sp_serveroption @server=@lsRemote
           , @optname=N'collation compatible', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'lazy schema validation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'remote proc transaction promotion'
       , @optvalue=N'false'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @lsRemote
       , @locallogin = NULL 
       , @useself = N'True'

print  @tab + 'Added Self Server as Linked Server ' + @lsRemote

Processing – Create Database (DBLab)


use [master]
go

if db_id('DBLab') is null
begin

	print 'Creating DB - DBLab .... '

	exec('create database [DBLab]')

	exec('ALTER DATABASE [DBLab] set recovery simple')

	print 'Creating DB - DBLab'

end
go

Processing – Table – dbo.whatisonyourmind (Create)

set noexec off
go

use [DBLab]
go

if object_id('dbo.whatisonyourmind') is not null
begin

	set noexec on
end
go

create table dbo.whatisonyourmind
(
	  [post] nvarchar(600)

	, [addedBy] sysname not null 
		constraint defaultWhatIsOnYourMindAddedBy default SYSTEM_USER

	, [addedOn] datetime not null 
		constraint defaultWhatIsOnYourMindAddedOn default getutcdate()

)

go

set noexec off
go

Processing – Table – dbo.whatisonyourmindArchive (Create)


set noexec off
go
use [DBLab]
go

if object_id('dbo.whatisonyourmindArchive') is not null
begin
	set noexec on
end
go

/*
	drop table dbo.whatisonyourmindArchive
*/
create table dbo.whatisonyourmindArchive
(

	  [id] bigint not null identity(1,1)

	, [post] nvarchar(600)

	, [addedBy] sysname not null 

	, [addedOn] datetime not null 

	, [modificationType] char (1) not null

	, [archivedBy] sysname not null 
		constraint defaultWhatIsOnYourMindArchivedBy default SYSTEM_USER

	, [dateArchived]  datetime not null 
		constraint defaultWhatIsOnYourMindArchivedOn default getutcdate()

)

go

set noexec off
go

Trigger

Here is a trigger the captures changes occurring on dbo.whatisonyourmind and propagates them unto dbo.whatisonyourmindArchive.


set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_Whatisonyourmind
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        --post changes unto dbo.whatisonyourmindArchive
	insert into dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

        /* process insert/updates as 1 */
	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

        /* remove records that are being deleted */
	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

Data Changes

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('Dev1819')

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV1819.2')

update [dbo].[whatisonyourmind]
set    [post] = 'DEV1819.3'
where  [addedBy] = SYSTEM_USER

delete
from   [dbo].[whatisonyourmind]
where  [post] = 'DEV1819.3'

select *
from   [dbo].[whatisonyourmind]

select *
from   [dbo].[whatisonyourmindArchive]

everything is good!

Trigger – Link Server

A few months later we get a request that we should decouple our OLTP (dbo.whatisonyourmind) and OLAP System (dbo.whatisonyourmindArchive).

And, we created the Linked Server mentioned earlier and change the trigger code a bit.

Here is our new trigger:



set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_WhatisonyourmindLS
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        /* insert into dbo.whatisonyourmind */
	insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

The basic changes between this new trigger and our earlier one is:

  • Changed “insert into dbo.whatisonyourmind” to “insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive”

Data Changes (Failed)

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV6834')

Upon, a quick test, nothing is good, as we get the error pasted below:


Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Code Repository

To facilitate quick and broad participation and review,  I have shared this code in github. And, it is available in https://github.com/DanielAdeniji/MSSQLServerLinkedServerLBTrigger

 

Lab Environment

This problem has been tested out in the following environments:

  • Microsoft SQL Server 2012 – SP1 {11.0.2100}
  • Microsoft SQL Server 2008/R2 – SP2 {10.50.4000}
  • Microsoft SQL Server 2000 – SP4 {8.0.2039}

 

Summary

This seems to be a problem and I have been able to reproduce on a couple of MS SQL Server Instances; both of them running MS SQL Server 2012.

And, thus I opened a Connect Item – “Transaction context in use by another session (when LoopBack LinkedServer is used in trigger” {https://connect.microsoft.com/SQLServer/feedback/details/814524/transaction-context-in-use-by-another-session-when-loopback-linkedserver-is-used-in-trigger} to track.

Addendum

2014-04-09 – Connect Feedback – Alexey Stepanov

Alexey commented on the Connect Item and pointed out two Technet Articles that discusses the ‘handicap’:

References

References – Linked Server – Loopback

SQL Server – Error – “Auto Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.” – Msg 512, Level 16, State 1

Introduction

This error is not really a big deal, but can use some of the methods mentioned below to streamline your code.

SQL

 

SQL  – Table Creation


if OBJECT_ID('dbo.sales') is null
begin

create table dbo.sales
(
      [partitionID] int
    , [salesPersonID] int
)

end
go

SQL  – Statement Insert


set nocount on;

truncate table dbo.sales

insert into dbo.sales
([partitionID], [salesPersonID])
values (10, 120)
go	

insert into dbo.sales([partitionID], [salesPersonID])
values (20, 140);

insert into dbo.sales([partitionID], [salesPersonID])
values (30, 160);
go	

insert into dbo.sales([partitionID], [salesPersonID])
values
	  (40, 240)
	, (50, 260)
	, (60, 280);	
go	

Error

When we run the multi-statement insert, we run into the error pasted below:

Error Message

Msg 512, Level 16, State 1, Procedure TR_Sales, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.

Error Source

We traced the error back to the Trigger on the dbo.sales table.


if OBJECT_ID('[dbo].[TR_Sales_SetPartitionID]') is not null
begin
	drop trigger [dbo].[TR_Sales_SetPartitionID]
end
go

CREATE TRIGGER [dbo].[TR_Sales_SetPartitionID]
	ON  [dbo].[sales] 
	AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @salesPersonId INT
    DECLARE @partitionID INT

    begin
	SET @salesPersonId = (SELECT SalesPersonID FROM inserted)

        set @partitionID = rand(cast(checksum(newid()) as bigint))
                 * @salesPersonId

	IF ( (SELECT partitionID FROM inserted) IS NULL )
	BEGIN

		UPDATE [dbo].[sales] 
		SET    partitionID = @partitionID
		WHERE   salesPersonID = @salesPersonID

	END

   end
END

Error Diagnosis

  • Traced the error back to the SQL that reads “set @salesPersonID = ( select personID from inserted”
  • The select line has problems when we are trying to insert more than one record within the same batch; we thus end up with Number of records in the Virtual table (inserted).  The system is not able to place N entries in the salePersonID column into a single variable (@salesPersonID)

Corrections

Corrected Trigger

We changed the problematic SQL to ensure that a single record is returned:

Revision Snippet


SET @salesPersonId = 
      (
	  SELECT SalesPersonID 
	  FROM   @tblList
	  where  [id] = @id
      )

Full Revision


if OBJECT_ID('[dbo].[TR_Sales_SetPartitionID]') is not null
begin
	drop trigger [dbo].[TR_Sales_SetPartitionID]
end
go

CREATE TRIGGER [dbo].[TR_Sales_SetPartitionID]
	ON  [dbo].[sales] 
	AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @salesPersonId INT
    DECLARE @partitionID INT

    declare
	  @id bigint
	, @IdMax bigInt

   DECLARE @tblList TABLE
   (
	 [id] bigint not null identity(1,1)
	,[SalesPersonID] int not null
	,[partitionID] int null
   )			

   begin

	insert into @tblList
	(
		[SalesPersonID]
	)
	select
		SalesPersonID
	from  inserted

	set @id =1
	set @IdMax = ( select MAX(id) from @tblList)

	while (@id <= @IdMax)
	begin

	    /*
		SET @salesPersonId = (SELECT SalesPersonID FROM inserted)

		set @partitionID = Rand(@salesPersonID) * 10010
	    */

	   SET @salesPersonId = (
					SELECT SalesPersonID 
					FROM   @tblList
					where  [id] = @id
			       )

	  set @partitionID = rand(cast(checksum(newid()) as bigint)) * @salesPersonId

	  update @tblList
	  set    [partitionID] = @partitionID 
	  where  [id] = @id

 	  set @id = @id + 1
       end

      --IF ( (SELECT partitionID FROM inserted) IS NULL )
      BEGIN

	   UPDATE tblSales
	   SET    partitionID = @partitionID

	   FROM   [dbo].[sales] tblSales

		inner join @tblList tblList
			on tblSales.salesPersonID = tblList.SalesPersonID

	  WHERE  tblSales.partitionID is null

     END

   end

END

go

Corrected Trigger – Set

We changed the problematic SQL to utilize Sets.  This is possible because the work we were doing within the Trigger can be maintained within a simple SQL.

Full Revision


if OBJECT_ID('[dbo].[TR_Sales_SetPartitionID]') is not null
begin
	drop trigger [dbo].[TR_Sales_SetPartitionID]
end
go

CREATE TRIGGER [dbo].[TR_Sales_SetPartitionID]
	ON  [dbo].[sales] 
	AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    begin

	UPDATE tblSales

	SET    partitionID = rand(cast(checksum(newid()) as bigint)) 
                         * tblInserted.salesPersonId

	FROM   [dbo].[sales] tblSales

		inner join inserted tblInserted

			  on tblSales.salesPersonID = tblInserted.salesPersonID

	WHERE  tblSales.partitionID is null

   end

END