Logging into table in SQL Server trigger?

Another possible option is to use a table variable to capture the info you want to store in your permanent log table. Table variables are not rolled back if a ROLLBACK TRANSACTION command is given. Sample code is 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.

Another possible option is to use a table variable to capture the info you want to store in your permanent log table. Table variables are not rolled back if a ROLLBACK TRANSACTION command is given. Sample code is 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.

This is what we do – HLGEM May 11 '10 at 19:58 Indeed. I've missed this option, but vote for it. – AlexS May 11 '10 at 20:04.

The problem here is that logging is part of transaction that modifies your data. Nested transactions will not help here. What you need is to put you logging actions into a separate context (connection), i.e.

Make it independent from you current transaction. Two options come to my mind: use Service Broker for logging - put log data to queue, receive and save the data 'on the other side of the pipe' (i.e. In another process/connection/transaction) use OPENQUERY - you will need to register you own server as a 'linked server' and execute queries 'remotely' (I know, this looks a little bit strange, but an option anyway ...) HTH.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.