Sql-server – Transaction independent writes inside a trigger

sql-server-2008sql-server-expresstrigger

I am using sql express 2008 r2 express edition.

Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire?

I have triggers working for simple auditing on some tables in my DB. I have one table for keeping the auditing data (Look here for details) It works fine except i don't want the auditing data be vanished when the transaction rolledback. So I want to write audit data to audit table independent from the transaction of the statement that causes my auditing trigger to fire.

Best Answer

One solution is to add a linked server to your own sever. On the linked server, disable transactions. Any calls made to the linked server will now run in their own transaction.

Example setup:

-- Add linked server called LOCALHOST
USE [master]
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LOCALHOST')
    EXEC master.dbo.sp_dropserver @server=N'LOCALHOST', @droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
-- Allow procedure calls
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc out', @optvalue=N'true'
-- But do not allow the procedure calls to enlist in our transaction
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'remote proc transaction promotion', @optvalue=N'false'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LOCALHOST', @locallogin = NULL , @useself = N'true'

-- Create a test procedure
use TestDatabase
if exists (select * from sys.tables where name = 'Log')
    drop table log
if exists (select * from sys.procedures where name = 'WriteLog')
    drop procedure WriteLog
go
create table log (id int identity, message varchar(256))
go
create procedure WriteLog
as
insert Log (message) values ('Hello World!')
go

Test code:

-- Insert through a direct call, and then through a linked server call
begin transaction
exec TestDatabase.dbo.WriteLog
exec [Localhost].TestDatabase.dbo.WriteLog
rollback transaction

select * from Log

The select will show only 1 row. The rollback has not undone the effefcts of the linked server call.

While a nice trick, this is probably not something you'd want to do inside a production environment. Personally I'd only allow data modifications through a stored procedure, and add logging in the stored procedure itself, before it starts a transaction (if any.)

Related Question