SQL Server: update trigger fires before insert trigger

Question

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

• Ali Razeghi - AWS: How about making the procedure where it writes to the logging table first, then the base table, if I understood your issue correctly.
• blobbles: This doesn't make sense (well, to me!). Theoretically the after insert trigger should work only on INSERT's. The after update trigger only on UPDATE's. Never the twain should meet. It sounds to me like you are inserting data, then the update trigger is firing before the insert trigger. Is this what you are saying? Although you say " how is it possible for the update trigger to create an audit entry before the insert trigger?", is the thing you are doing to cause this an insert on a table?
• dlh: That's exactly what I'm saying. I am seeing UPDATE audit records preceding the corresponding INSERT audit records. It didn't make sense to me either and I'm trying to figure out how that could be possible. I will rephrase the question to make this clearer.
• Kenneth Fisher: Can you post the structure of one of the tables? Specifically any constraints, defaults etc? And are there any other triggers in place other than these two?
• John K. N.: I think your answer might be found here: dba.stackexchange.com/q/468/15356

Answer 1

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:

1. 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?

2. Is it possible that the UPDATE Trigger is somehow defined as being AFTER INSERT, UPDATE ? If so, an INSERT operation would fire both triggers making it look like both an INSERT and an UPDATE happened when in fact there was no UPDATE operation (which might also explain why the times are only a few milliseconds apart for the audit entries).

3. Is it possible that your query to determine "corresponding" records is flawed and that the results are misleading?

Answer 2

I just had this exact scenario happen and figured out what was going on (at least for my situation). I had an old, third trigger on my table that was something like this:

CREATE TRIGGER [dbo].[MyTrigger_fi] ON [dbo].[theTable] 
FOR INSERT
AS
UPDATE theTable
SET ReportingDate = ot.ReportingDate
FROM Inserted i
JOIN theTable tb ON tb.ID = i.ID
JOIN OtherTable ot on ot.ID = tb.OtherTableID

That FOR INSERT was happening before my AFTER INSERT, performing the update, which then fired my AFTER UPDATE trigger. The end result was an Update message being logged before my Insert message on every insert. It all went down like this:

Record Inserted
 FOR INSERT trigger fires
 Record is Updated
 AFTER UPDATE trigger fires
 Update Event is recorded
 AFTER INSERT trigger fires
 Insert Event is recorded

• Solomon Rutzky: Very interesting. Thanks for sharing this.

Answer 3

If I understand your issue, it's that the 'after trigger' in SQL Server will pass the immediate current datetime which is a few ms after the actual event took place. How do you make the them match.

You could try getting the datetime as a variable in the beginning of 1 transaction and passing that value to the auditing table. Would that work for you? That way it's what it was when the transaction started.

You could have a last updated column enforced with a trigger any the applicable columns on the row. Then you get the 'last updated' value and finally, your 2nd trigger would copy both values to the auditing table. This would require nested triggers however which is often a no no. I personally would go with getting the datetime as a variable and passing that along using transactions where needed.