Entering a table in a SQL Server trigger

Question

I am encoding a SQL Server 2005 trigger. I want to do some logging while the trigger is running using the INSERT statement in my log table. If an error occurs at runtime, I want to raise the error and cancel the action that causes the trigger to start, but not to lose the log entries. What is the best way to achieve this?

Now my trigger registers everything except when there is an error - due to ROLLBACK. A RAISERROR statement is required to inform the caller of an error.

Now my error handling code looks like this:

if (@err = 1) begin INSERT INTO dbo.log(date, entry) SELECT getdate(), 'ERROR: ' + out from #output RAISERROR (@msg, 16, 1) rollback transaction return end 

Answer 1

Another possible option is to use a table variable to capture the information you want to keep in your persistent log table. Table variables are not returned if the ROLLBACK TRANSACTION command is given. Sample code below ...

 --- Declare table variable DECLARE @ErrorTable TABLE ( [DATE] smalldatetime, [ENTRY] varchar(64) ) DECLARE @nErrorVar int --- Open Transaction BEGIN TRANSACTION --- Pretend to cause an error and catch the error code SET @nErrorVar = 1 --- @@ERROR IF (@nErrorVar = 1) BEGIN --- Insert error info table variable INSERT INTO @ErrorTable ( [Date], [Entry] ) SELECT getdate(), 'Error Message Goes Here' RAISERROR('Error Message Goes Here', 16, 1) ROLLBACK TRANSACTION --- Change this to actually insert into your permanent log table SELECT * FROM @ErrorTable END IF @@TRANCOUNT 0 PRINT 'Open Transactions Exist' ELSE PRINT 'No Open Transactions' 

Answer 2

The problem is that logging is part of a transaction that modifies your data. Nested transactions will not help here. You need you to enter the actions for logging in a separate context (connection), i.e. Made it independent of the current transaction.

Two options come to me:

• use Service Broker for logging - put the log data in a queue, receive and save data "on the other side of the pipe" (ie in another process / connection / transaction).
• use OPENQUERY - you will need to register your server as a “linked server” and execute the requests “remotely” (I know this looks a bit strange, but an option anyway ...)

NTN

Answer 3

I don’t know if I think it’s too simple, but why not just change the order of the error handler to insert AFTER rollback?

 if (@err = 1) begin RAISERROR (@msg, 16, 1) rollback transaction INSERT INTO dbo.log(date, entry) SELECT getdate(), 'ERROR: ' + out from #output return end 

Answer 4

Checkout error handling in triggers .