Transaction independent writes inside a trigger

Question

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.

Just in case an Oracle user ever stumbles across this question - you can do this in Oracle using an autonomous transaction. — Philᵀᴹ, Mar 23 '12 at 14:00
Technically this can be accomplished via dynamic SQL, but I would not do it. IMO is is way easier to handle errors and implement auditing logic outside the database. — A-K, Mar 23 '12 at 16:05

score 1 · Accepted Answer · answered Mar 23 '12 at 14:05

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.)

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

1

Use Service Broker to decouple the transaction from the log write.

That is, push the change info onto the Service broker queue to be consumed by some listener. More sample code in this dba.se answer

edited Apr 13 '17 at 12:42

Community

1

answered Mar 23 '12 at 15:16

gbn

69,809
8
163
243

I don't think it is possible in express editions – Serguzest Mar 23 '12 at 15:28
hmm. It can consume but not send. http://stackoverflow.com/questions/3559905/using-service-broker-with-sql-server-express-2008 – gbn Mar 23 '12 at 15:36
I'd expect rollback transaction to roll back any messages pushed to a queue? – Andomar Mar 23 '12 at 15:47
@Andomar: AFAIK, it doesn't. – gbn Mar 23 '12 at 15:48

Transaction independent writes inside a trigger

2 Answers2