Why some triggers run exactly at the same time?

0 votes
1 answer
96 views
                          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 = ''
    	DECLARE @oldAuditBody XML = ''
    	DECLARE @newAuditBody XML = ''
    	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?
                        
Asked by mostafa8026 (121 rep)
Mar 18, 2020, 07:49 AM
Last activity: Mar 18, 2020, 11:31 AM
Why some triggers run exactly at the same time?

Related Questions
