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?