July 25, 2005 at 6:47 am
I've got a table (REPORT) with a couple of date columns - DateCreated and DateModified. I set DateCreated using a Default value to GetDate() and DateModified using an INSERT, UPDATE trigger and GetDate(). Now I also want to keep an audit trail on this table so I have a second table (REPORTHISTORY) with the same schema except that it has an additional primary key column (an IDENTITY).
To generate the audit trail I have another INSERT, UPDATE trigger on the REPORT table that inserts from INSERTED into REPORTHISTORY.
My question is this: How can I be sure that my two date fields will have todays date in the case of DateCreated and the Default value and a new record and DateModified via the first trigger and an updated record?
July 25, 2005 at 7:05 am
You can use sp_settriggerorder to set the order in which a trigger is fired...
EXEC sp_settriggerorder <trigger name>, <order>, '<operation>'
-- M Kulangara
Mathew J Kulangara
sqladventures.blogspot.com
July 26, 2005 at 6:58 am
If you have an AuditTrail table then you don't need a modified date in your 'live data'.
Your AuditTrail table should be the same as your primary or live table except at the end you will add the following fields;
AuditDate
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
July 26, 2005 at 10:31 am
Yes, that makes sense.
Thank you both for your responses
Gary
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply