Why some triggers run exactly at the same time?

Question

I wrote a simple audit with triggers. when a trigger called, the data inside the trigger (inserted and deleted rows) are sent through broker to a procedure to write them in the Master Audit Table. here is the trigger:

CREATE TRIGGER [dbo].[trg_personAudit] 
ON [dbo].[person]  
 AFTER INSERT, UPDATE, DELETE 
AS
    DECLARE @auditBody XML = '<AuditMsg/>'
    DECLARE @oldAuditBody XML = '<AuditMsg/>'
    DECLARE @newAuditBody XML = '<AuditMsg/>'
    DECLARE @DMLType CHAR(1)
    DECLARE @tableName sysname 
    DECLARE @guid varchar(100) = NULL;

    BEGIN
        DECLARE @change_date datetime2(7) = sysdatetime();
        SET @DMLType = 'I'
        IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) 
            SET @DMLType = 'U';
        ELSE IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) 
            SET @DMLType = 'I';
        ELSE
            SET @DMLType = 'D';

        declare @userid nvarchar(20)

        select @userid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'userid=%'
        select top 1 @userid=splitdata from dbo.fnSplitString(@userid,'=') ORDER BY splitdata asc
        select @guid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'gid=%'
        select top 1 @guid=splitdata from dbo.fnSplitString(@guid,'=') ORDER BY splitdata asc

        IF (@userid IS NULL OR @userid = '')
            SELECT @userid = '-1'

        SELECT  @oldAuditBody = (SELECT * FROM deleted  FOR XML AUTO, BINARY BASE64, ELEMENTS)
        SELECT  @newAuditBody = (SELECT * FROM inserted  FOR XML AUTO, BINARY BASE64, ELEMENTS)

        SET @tableName = 'person'

        print 'generated guid is: ' + @guid;
        SELECT @auditBody = (select 
                                DB_NAME() as SourceDb,
                                @tableName as SourceTable,
                                @userid as UserId,
                                @DMLType as DMLType,
                                @oldAuditBody as OldData,
                                @newAuditBody as NewData,
                                @guid as gid,
                                @change_date as ChangeDate
                            for xml path('AuditMsg'))

        EXEC dbo.usp_SendAuditData @auditBody
    END

GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'DELETE'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'INSERT'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'UPDATE'

as you can see, I get the current time stamp in this line:

DECLARE @change_date datetime2(7) = sysdatetime();

Suppose there is an insert and after that we have an update: sometimes this two triggers run exactly at the same time. even we have datetime2(7) but the times of the both rows are exactly the same. I can't find any solution to overcome this situation. Why the times are exactly equal?

Answer 1

when you use the MERGE statement it triggers all three types of triggers (INSERT / UPDATE / DELETE), but I'm not sure, if it calls a combined trigger three times too, but this should be easy to find out...