36 Chambers – The Legendary Journeys: Execution to the max!

April 22, 2014

Accessing The INSERTED And DELETED Pseudo-Tables In A Trigger’s CATCH Block

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

At work recently, we had a scenario come up in which we wanted to figure out which specific values are causing a trigger to roll back execution of a stored procedure.  I decided to put together a test case to see if I could still access the DELETED pseudo-table from within a trigger’s CATCH block.  It turns out that you can.

CREATE TABLE [Test]
(
	Id INT,
	Val CHAR(5)
);

INSERT into [dbo].[Test]
(
	Id,
	Val
)
VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc'), (4, 'ddddd');

CREATE TRIGGER tr_test on [Test]
after update
AS
BEGIN
	BEGIN TRY
		IF EXISTS(SELECT * FROM DELETED WHERE Id = 5)
		BEGIN
			THROW 50000, 'Ayup', 1;
		END
	END try
	BEGIN catch
		SELECT
			*
		FROM DELETED;
	END catch
END
GO

--This first run should NOT result in any errors.
UPDATE dbo.Test
SET
	Id = Id + 4;

--This second run SHOULD result in an error, showing us 4 records.
UPDATE dbo.Test
SET
	Id = Id + 4;

TRUNCATE TABLE Test;
DROP TABLE Test;

The trick here is that if you manually issue a ROLLBACK statement inside your CATCH block, you need to do it after collecting the information you need from the DELETED and INSERTED psuedo-tables, as those get cleared out upon rollback. If you insert those records into a variable (either a table variable or a regular variable using something like FOR XML PATH to concatenate the results), you can continue to use them in the CATCH block after you roll back your statement. That way, you can log the error without having that log roll back.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 94 other followers

%d bloggers like this: