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.