I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to assist in debugging.
I'm using after
triggers. Below are simplified versions of my triggers.
Question: I am seeing update
audit records that precede the corresponding insert
audit records. How is this possible? The difference is only a few miliseconds and doesn't matter for my current purposes, but I can imagine much worse scenarios where program logic depends on the correct chronology.
I know about the ways to control trigger execution order among triggers of the same kind (all insert
or all update
). What assumptions can I make about heterogeneous trigger execution order?
create trigger dbo.MyTrigger_i on dbo.theTable
after insert
as
begin
set nocount on
declare @Date datetime, @User sysname
set @Date = GETDATE()
set @User = SUSER_SNAME()
insert into MyAudit (RowID, [Date], UserName, Comment)
select i.ID, @Date, @User, 'Insert'
from
inserted as i
end
go
create trigger dbo.MyTrigger_u on dbo.theTable
after update
as
begin
set nocount on
declare @Date datetime, @User sysname
set @Date = GETDATE()
set @User = SUSER_SNAME()
insert into MyAudit (RowID, [Date], UserName, Comment)
select
i.ID, @Date, @User, 'Update'
from
inserted as i
inner join deleted as d
on i.ID = d.ID
end
go
Best Answer
Considering that a) Triggers are naturally part of the Transaction that is the DML statement that fired the Trigger, and b) an UPDATE cannot happen on a row until the row exists, it is impossible for the actual UPDATE to show up before the actual INSERT. Hence, something else is going on.
Things to consider:
Are the Trigger definitions shown in the question the actual and current definitions? Is it possible that the
Comment
values of "Insert" and "Update" are switched in the Triggers such that the INSERT Trigger has the comment of "Update" and vice-versa?Is it possible that the
UPDATE
Trigger is somehow defined as beingAFTER INSERT, UPDATE
? If so, anINSERT
operation would fire both triggers making it look like both anINSERT
and anUPDATE
happened when in fact there was noUPDATE
operation (which might also explain why the times are only a few milliseconds apart for the audit entries).Is it possible that your query to determine "corresponding" records is flawed and that the results are misleading?