SQL 2008 Triggers

[guest2013-1]

I have an AFTER trigger that fires and simply updates an audit log. However, recently, it has come to light that what is in the audit log doesn't reflect what is in the table. So I started looking around.

The trigger fires for this (and inserts into the audit log, it's a stored procedure that updates the record):

BEGIN TRAN h
update myTable set Col1 = @rnd where Col2 = 1
--Pretend something went wrong
ROLLBACK TRAN h

But it doesn't fire again when ROLLBACK is called

This means my audit picks up the edit of the record, but it doesn't show that it went back to the original value before the edit because of the transaction that was rolled back.

Kind of annoying. Any ideas on how to approach this issue?

 

[kripstoe]

Don't use a trigger and manually update the audit log when the TRAN was successful?

 

[guest2013-1]

Don't use a trigger and manually update the audit log when the TRAN was successful?

The problem with that is we're a team of several developers and some outsourced people as well. I'm unable to dictate what/how they write their stored procedures (trust me, I've tried) and that is one of the reasons why I have an audit log on some of our tables since users are moaning about data integrity and if they could trust the data or not.

The only spot I can control this without having to edit 100's of stored procedures and keep checking if someone updated/overwrote the audit code, is on a trigger that fires automatically

 

[Mr.Jax]

What am I missing here ?
If you roll back, your audit inserts also roll back.

I did this quick test and everything checks out just fine.

drop table test_tab
drop table test_tab_audit
create table test_tab
(
id int identity(1,1) not null primary key,
name nvarchar(100) not null
)
go
create table test_tab_audit
(
id int identity(1,1) not null primary key,
oldname nvarchar(100) not null,
newname nvarchar(100) not null
)
go
create trigger test_tab_audit_trig
on tesT_tab
after insert
as
begin
insert into test_tab_audit(oldname, newname)
select '', name from inserted
end
go


create trigger test_tab_audit_trig_u
on tesT_tab
after update
as
begin
insert into test_tab_audit(oldname, newname)
select d.name, i.name
from inserted i join deleted d on d.id = i.id
end
go


insert into test_tab(name) select 'BOSSRECORD'
begin transaction a
update test_tab set name='BOSSRECORD2' where name='BOSSRECORD'
insert into test_tab(name) select 'test1'
insert into test_tab(name) select 'test2'
insert into test_tab(name) select 'test3'
select * from test_tab_audit -- here you see the expected audit record, all inserts and the 1 update
rollback transaction a

select * from test_tab_audit -- here you only see 1 record; the bossrecord insert. So the rollback kept data integrity!

 

[guest2013-1]

Our audit logs is on a different system and I use a CLR stored procedure in the trigger to send the message to MSMQ for further processing so there is no direct linked servers required as some of our sites are offsite from the main servers.

When the transaction is rolled back, it doesn't fire at all.

 

[guest2013-1]

I have AFTER INSERT, UPDATE, DELETE

So it's one trigger that fires. I just did a simply "print 'test'" and it only shows once for when it initially fires the update, on rollback, there is nothing printed.

 

[Mr.Jax]

Yup, that's how MSSQL (and oracle) behaves;

As a possible solution, I'd have the triggers populate audit details to a local table; and then have a batch process that periodically pulls out the new data, calling your clr proc.

 

[guest2013-1]

Yea, that was going to be my fallback solution, but I wanted to avoid that at all costs as much as possible because then I'm subject to jobs and maintaining them. Will have to think about it. Thanks for clarifying

 

[zippy]

Rollback applies to the entire transaction for that session. Working as intended

Oracle has a feature called Pragma autonomous transaction which you place in the declaration section of the procedure. You then have to have a commit in that procedure.

It causes only the updates etc. In that proc to be committed if there is a rollback.

Sqlserver should have something similar.

However, there are issues when called across database links.

 

[guest2013-1]

Rollback applies to the entire transaction for that session. Working as intended

Oracle has a feature called Pragma autonomous transaction which you place in the declaration section of the procedure. You then have to have a commit in that procedure.

It causes only the updates etc. In that proc to be committed if there is a rollback.

Sqlserver should have something similar.

However, there are issues when called across database links.

SQL Server doesn't have something similar, but you can (with some work) get the same result.

The problem I have is a bit different, and I don't think it's solvable by what you suggest. Basically what happens is a table gets an update (or insert or delete). The trigger fires, as it's supposed to, and I get a message "Record 1 has been updated"

However, when you encapsulate the insert/update/delete you're doing on the table in a transaction, and it fails for whatever reason and you do a rollback, the trigger doesn't fire again going "Record 1 has been updated" (even if it's back to the original content)

It will fire on uncommitted changes regardless, and not fire again for changes if it does rollback (because the DB pretends like it never happened and doesn't bother firing the trigger again)

Personally I'd have preferred the trigger being fired on only committed data, since (in my mind) it makes sense that something that didn't change yet, shouldn't be "announced" by the trigger.

Anyway, I'm playing around with what Jax suggested. I created a hold table, the trigger will write to it with (rowlock) and the job sick that fires will read with (readpast) so that it only takes committed inserts/updates and deletes

It will slow down the response time of my audit log (because management liked the fact that if they made a change, 1 second later it's already logged and audited and available to be reported on), but I guess to get the audit trail 100%, we need to make that sacrifice

I did notice integration software like eConnect and Scribe do more or less the same thing (with the hold table) and wondered why they didn't just do it in trigger form direct to their queues... now I know.

 

[guest2013-1]

Hopefully this thread helps someone, because there is no clear answer (I could find anyway) of if a trigger fires on rollback on a transaction. Only a bunch of tutorials on how to do rollbacks in triggers.

Using with (ROWLOCK) to insert the record into a hold table, and a job that reads the table to process it using with (READPAST), I was able to ignore any uncommitted transactions and process the messages successfully