0

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?

2
  • This might help?
    – Vérace
    Mar 18, 2020 at 8:28
  • thanks. but I wonder why sometimes are not equal. Mar 18, 2020 at 8:52

1 Answer 1

0

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

1
  • I don't use MERGE statements. Mar 18, 2020 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.