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

```

Answer: 
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

```
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](https://msdn.microsoft.com/en-us/library/ms178101.aspx) 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.