Incorrect AuditDate

May 28, 2009 at 9:34 PM

I am trying to use AutoAudit v1.09a as the basis for auditing data changes in our application. It is working well except that the AuditDate for an updated record is not correct.  The ModifiedDate in the master table is being set correctly.  However, the date that is being captured in the Audit table is the previous modification date, not the current modification date.

 

ALTER TRIGGER [Dictionary].[trg_Excusal_Modified] ON [Dictionary].[tblExcusal]

 AFTER Update

 NOT FOR REPLICATION AS

 SET NoCount On

select * from inserted

print '[Dictionary].[trg_Excusal_Modified]-1'

DECLARE @AuditTime DATETIME

SET @AuditTime = GetDate()

print '            GetDate() = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Deleted)

print ' Deleted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Inserted)

print 'Inserted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

Begin Try

 If Trigger_NestLevel(object_ID(N'[Dictionary].[trg_Excusal_Modified]')) > 1 Return;

print '[Dictionary].[trg_Excusal_Modified]-2'

 If (Update(CreatedDate) or Update(ModifiedDate)) AND Trigger_NestLevel() = 1

 Begin; Raiserror('Update failed.', 16, 1); Rollback; Return; End;

 -- Update the Modified date

print '[Dictionary].[trg_Excusal_Modified]-3'

 UPDATE [Dictionary].[tblExcusal]

 SET ModifiedDate = getdate()

 FROM [Dictionary].[tblExcusal]

 JOIN Inserted

   ON [tblExcusal].[ExcusalID] = Inserted.[ExcusalID]

print '[Dictionary].[trg_Excusal_Modified]-4'

End Try

Begin Catch

  Raiserror('error in [Dictionary].[trg_Excusal_Modified] trigger', 16, 1 ) with log

End Catch

print '[Dictionary].[trg_Excusal_Modified]-5'

 

ALTER TRIGGER [Dictionary].[trg_Excusal_Audit_Update] ON [Dictionary].[tblExcusal]

 AFTER Update

 NOT FOR REPLICATION AS

 SET NoCount On

print '[Dictionary].[trg_Excusal_Audit_Update]-1'

select * from inserted

DECLARE @AuditTime DATETIME

SET @AuditTime = GetDate()

print '            GetDate() = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Deleted)

print ' Deleted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Inserted)

print 'Inserted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

Begin Try

 IF UPDATE([ExcusalID])

   INSERT Audit.tblAudit (AuditDate, ServerInstanceName, HostName, SysUserName, AppName, SchemaName, TableName, Operation, AppUserID, PrimaryKey, ColumnName, OldValue, NewValue, [RowVersion], AuditRowGUID)

   SELECT  Inserted.ModifiedDate, @@SERVERNAME, Host_Name(), suser_sname(), APP_NAME(), '[Dictionary]', '[tblExcusal]', 'u', Inserted.ModifiedUserID, Inserted.[ExcusalID],

     '[ExcusalID]', Convert(VARCHAR(MAX), Deleted.[ExcusalID]),  Convert(VARCHAR(MAX), Inserted.[ExcusalID]),

     Deleted.[RowVersion], Deleted.rowguid

   FROM Inserted

     JOIN Deleted

     ON Inserted.[ExcusalID] = Deleted.[ExcusalID]

     AND COALESCE(Inserted.[ExcusalID],'') <> COALESCE(Deleted.[ExcusalID],'')

 

 IF UPDATE([Description])

   INSERT Audit.tblAudit (AuditDate, ServerInstanceName, HostName, SysUserName, AppName, SchemaName, TableName, Operation, AppUserID, PrimaryKey, ColumnName, OldValue, NewValue, [RowVersion], AuditRowGUID)

   SELECT  Inserted.ModifiedDate, @@SERVERNAME, Host_Name(), suser_sname(), APP_NAME(), '[Dictionary]', '[tblExcusal]', 'u', Inserted.ModifiedUserID, Inserted.[ExcusalID],

     '[Description]', Convert(VARCHAR(MAX), Deleted.[Description]),  Convert(VARCHAR(MAX), Inserted.[Description]),

     Deleted.[RowVersion], Deleted.rowguid

   FROM Inserted

     JOIN Deleted

     ON Inserted.[ExcusalID] = Deleted.[ExcusalID]

     AND COALESCE(Inserted.[Description],'') <> COALESCE(Deleted.[Description],'')

 

End Try

Begin Catch

  Raiserror('error in [Dictionary].[trg_Excusal_Audit_Update] trigger', 16, 1 ) with log

End Catch

print '[Dictionary].[trg_Excusal_Audit_Update]-2'

 

If I run this statement:

 

update Dictionary.tblExcusal set

 Description='test1'

where ExcusalID=7

 

I get this result:

[Dictionary].[trg_Excusal_Modified]-1

            GetDate() = 17:15:27:233

 Deleted.ModifiedDate = 16:58:51:183

Inserted.ModifiedDate = 16:58:51:183

[Dictionary].[trg_Excusal_Modified]-2

[Dictionary].[trg_Excusal_Modified]-3

[Dictionary].[trg_Excusal_Audit_Update]-1

            GetDate() = 17:15:27:233

 Deleted.ModifiedDate = 16:58:51:183

Inserted.ModifiedDate = 17:15:27:233

[Dictionary].[trg_Excusal_Audit_Update]-2

[Dictionary].[trg_Excusal_Modified]-4

[Dictionary].[trg_Excusal_Modified]-5

[Dictionary].[trg_Excusal_Audit_Update]-1

            GetDate() = 17:15:27:233

 Deleted.ModifiedDate = 16:58:51:183

Inserted.ModifiedDate = 16:58:51:183

[Dictionary].[trg_Excusal_Audit_Update]-2

 

(1 row(s) affected)

 

I tried added this statement, but it didn’t seem to have any effect:

EXEC sp_settriggerorder @triggername=N'[Dictionary].[trg_Excusal_Audit_Update]', @order=N'Last', @stmttype=N'UPDATE'

 

1.       Why are the update triggers executing in this sequence?

2.       Why is Inserted.ModifiedDate being reset?

3.       How can I capture the correct AuditDate?

4.       Have you made an changes to AutoAudit since v1.09a?

 

Coordinator
May 28, 2009 at 10:24 PM

Hi Calvin,

A couple days ago I finally finished SQL Server 2008 Bible. IN the next few days I’m going to spend a day working on an update to AutoAudit, so you timing is good. If you’re available then I’ll pass some emails back and forth so you can help test the new version.

Please send your email to me.

pauln@sqlserverbible.com

-Paul Nielsen

SQL Server MVP

www.SQLServerBible.com

sign up for my free e-Newsletter

From: CalvinJones [mailto:notifications@codeplex.com]
Sent: Thursday, May 28, 2009 2:35 PM
To: pauln@sqlserverbible.com
Subject: Incorrect AuditDate [AutoAudit:57797]

From: CalvinJones

I am trying to use AutoAudit v1.09a as the basis for auditing data changes in our application. It is working well except that the AuditDate for an updated record is not correct. The ModifiedDate in the master table is being set correctly. However, the date that is being captured in the Audit table is the previous modification date, not the current modification date.

ALTER TRIGGER [Dictionary].[trg_Excusal_Modified] ON [Dictionary].[tblExcusal]

AFTER Update

NOT FOR REPLICATION AS

SET NoCount On

select * from inserted

print '[Dictionary].[trg_Excusal_Modified]-1'

DECLARE @AuditTime DATETIME

SET @AuditTime = GetDate()

print ' GetDate() = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Deleted)

print ' Deleted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Inserted)

print 'Inserted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

Begin Try

If Trigger_NestLevel(object_ID(N'[Dictionary].[trg_Excusal_Modified]')) > 1 Return;

print '[Dictionary].[trg_Excusal_Modified]-2'

If (Update(CreatedDate) or Update(ModifiedDate)) AND Trigger_NestLevel() = 1

Begin; Raiserror('Update failed.', 16, 1); Rollback; Return; End;

-- Update the Modified date

print '[Dictionary].[trg_Excusal_Modified]-3'

UPDATE [Dictionary].[tblExcusal]

SET ModifiedDate = getdate()

FROM [Dictionary].[tblExcusal]

JOIN Inserted

ON [tblExcusal].[ExcusalID] = Inserted.[ExcusalID]

print '[Dictionary].[trg_Excusal_Modified]-4'

End Try

Begin Catch

Raiserror('error in [Dictionary].[trg_Excusal_Modified] trigger', 16, 1 ) with log

End Catch

print '[Dictionary].[trg_Excusal_Modified]-5'

ALTER TRIGGER [Dictionary].[trg_Excusal_Audit_Update] ON [Dictionary].[tblExcusal]

AFTER Update

NOT FOR REPLICATION AS

SET NoCount On

print '[Dictionary].[trg_Excusal_Audit_Update]-1'

select * from inserted

DECLARE @AuditTime DATETIME

SET @AuditTime = GetDate()

print ' GetDate() = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Deleted)

print ' Deleted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

SET @AuditTime = (select ModifiedDate from Inserted)

print 'Inserted.ModifiedDate = ' + convert(varchar, @AuditTime, 14)

Begin Try

IF UPDATE([ExcusalID])

INSERT Audit.tblAudit (AuditDate, ServerInstanceName, HostName, SysUserName, AppName, SchemaName, TableName, Operation, AppUserID, PrimaryKey, ColumnName, OldValue, NewValue, [RowVersion], AuditRowGUID)

SELECT Inserted.ModifiedDate, @@SERVERNAME, Host_Name(), suser_sname(), APP_NAME(), '[Dictionary]', '[tblExcusal]', 'u', Inserted.ModifiedUserID, Inserted.[ExcusalID],

'[ExcusalID]', Convert(VARCHAR(MAX), Deleted.[ExcusalID]), Convert(VARCHAR(MAX), Inserted.[ExcusalID]),

Deleted.[RowVersion], Deleted.rowguid

FROM Inserted

JOIN Deleted

ON Inserted.[ExcusalID] = Deleted.[ExcusalID]

AND COALESCE(Inserted.[ExcusalID],'') <> COALESCE(Deleted.[ExcusalID],'')

IF UPDATE([Description])

INSERT Audit.tblAudit (AuditDate, ServerInstanceName, HostName, SysUserName, AppName, SchemaName, TableName, Operation, AppUserID, PrimaryKey, ColumnName, OldValue, NewValue, [RowVersion], AuditRowGUID)

SELECT Inserted.ModifiedDate, @@SERVERNAME, Host_Name(), suser_sname(), APP_NAME(), '[Dictionary]', '[tblExcusal]', 'u', Inserted.ModifiedUserID, Inserted.[ExcusalID],

'[Description]', Convert(VARCHAR(MAX), Deleted.[Description]), Convert(VARCHAR(MAX), Inserted.[Description]),

Deleted.[RowVersion], Deleted.rowguid

FROM Inserted

JOIN Deleted

ON Inserted.[ExcusalID] = Deleted.[ExcusalID]

AND COALESCE(Inserted.[Description],'') <> COALESCE(Deleted.[Description],'')

End Try

Begin Catch

Raiserror('error in [Dictionary].[trg_Excusal_Audit_Update] trigger', 16, 1 ) with log

End Catch

print '[Dictionary].[trg_Excusal_Audit_Update]-2'

If I run this statement:

update Dictionary.tblExcusal set

Description='test1'

where ExcusalID=7

I get this result:

[Dictionary].[trg_Excusal_Modified]-1

GetDate() = 17:15:27:233

Deleted.ModifiedDate = 16:58:51:183

Inserted.ModifiedDate = 16:58:51:183

[Dictionary].[trg_Excusal_Modified]-2

[Dictionary].[trg_Excusal_Modified]-3

[Dictionary].[trg_Excusal_Audit_Update]-1

GetDate() = 17:15:27:233

Deleted.ModifiedDate = 16:58:51:183

Inserted.ModifiedDate = 17:15:27:233

[Dictionary].[trg_Excusal_Audit_Update]-2

[Dictionary].[trg_Excusal_Modified]-4

[Dictionary].[trg_Excusal_Modified]-5

[Dictionary].[trg_Excusal_Audit_Update]-1

GetDate() = 17:15:27:233

Deleted.ModifiedDate = 16:58:51:183

Inserted.ModifiedDate = 16:58:51:183

[Dictionary].[trg_Excusal_Audit_Update]-2

(1 row(s) affected)

I tried added this statement, but it didn’t seem to have any effect:

EXEC sp_settriggerorder @triggername=N'[Dictionary].[trg_Excusal_Audit_Update]', @order=N'Last', @stmttype=N'UPDATE'

1. Why are the update triggers executing in this sequence?

2. Why is Inserted.ModifiedDate being reset?

3. How can I capture the correct AuditDate?

4. Have you made an changes to AutoAudit since v1.09a?

Read the full discussion online.

To add a post to this discussion, reply to this email (AutoAudit@discussions.codeplex.com)

To start a new discussion for this project, email AutoAudit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com



__________ Information from ESET Smart Security, version of virus signature database 4113 (20090528) __________

The message was checked by ESET Smart Security.

http://www.eset.com



__________ Information from ESET Smart Security, version of virus signature database 4113 (20090528) __________

The message was checked by ESET Smart Security.

http://www.eset.com