| Author |
Topic |
|
ipisors
Starting Member
39 Posts |
 Posted - 2015-03-02 : 15:26:09
|
| I want to create a trigger in order to log changes on UPDATES to a certain table. I'm running into various hurdles conceptually, because as soon as I get traction on an idea that seems to fit the bill, the drawbacks start to pile up. - I'd really like to log changes to ALL FIELDS THAT WERE UPDATED (and only those) in an audit table with FIELDNAME, DATECHANGED, OLDVALUE, NEWVALUE but 2 questions arise: a) How do you control datatypes going into OLDVALUE and NEWVALUE? Cast everything as varchar and hope for the best? b) How does your trigger code figure out which fields got changed? Unfortunately SQL Server doesn't seem to have a ROW filler like Oracle would in their triggers.Please don't just reply that it's a bad idea unless you have an alternative solution. This particular table is impacted by developers (me included) in a rather uncontrolled environment which I cannot change at the moment. Accidents happen and that is what I am trying to backup data against. When someone accidentally runs an UPDATE statement, I'd like to have a possibility to recover the data that is efficient and subject to SQL querying without needing to wade thru SQL log files. Yes I know triggers can have an obvious impact on performance; I've already considered this and decided this is worth it and/or I want to try it anyway. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-02 : 15:33:57
|
| You can figure out which columns were updated using the UPDATE function, but I think you should change the design of your audit table so that it logs all columns, before/after values. You can figure out which ones were updated when you go to query it later by comparing before/after. For the BEFORE data, use the deleted trigger table. For the AFTER data, use the inserted trigger table. Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-03-02 : 15:41:56
|
| Tara, thank you for looking and commenting on this, I appreciate it.So:My audit table would look identical to my primary table. The trigger code would do an insert into the audit table(all fields) (select * from INSERTED - same fields). With presumably a DateChanged field added to the audit table?Or, do you mean my Audit table would be like my primary table as regards Data Types, but, have an Fieldname_Oldvalue and a Fieldname_Newvalue...and have the trigger code do ALL of this: - Insert into audit table all data from DELETED into Fieldname_Oldvalue- Update those same records in the audit table using all data from INSERTED ? Not sure how I would connect the insert-then-update - from a Date function maybe? |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-03-02 : 16:09:49
|
| I believe she is saying the audit table would have exactly the same structure as the original table. For example:main_table ( col1 int, col2 datetime, col3 nvarchar(30) )main_table_audit ( col1 int, col2 datetime, col3 nvarchar(30) )And the trigger inserts the changed columns in the row into main_table_audit. That's the method I would use, as it's much less logging overhead than column by column.But, if you want to do column by column logging, I'd suggest using sql_variant as the data type for the old and new values. |
|
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-03-02 : 16:26:21
|
| Gotcha. So the suggestion is for the audit table to have the same structure as primary table, and only insert the OLD values - (from internal trigger table DELETED) into those same fields? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-02 : 16:54:36
|
| It would be the same structure plus a few extra columns, such as updated_date (GETDATE()), updated_by (SUSER_NAME()), maybe it would lead off with an identity column too.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ipisors
Starting Member
39 Posts |
Posted - 2015-03-02 : 17:52:13
|
| Gotcha! Thank you. I've created the first version of the trigger to see how it goes and all is well - including the fields of USERCHANGED and DATECHANGED.Just from a general "making it practical and useful" perspective, I had 2 other questions piggy backing off of this:1) I understand the trigger will execute with updating user's permissions, unless otherwise specified in EXECUTE AS. I'm trying to figure out the implications of this and best ways to react. For all users who might make updates to the primary table, if I give them permissions to the audit table as well, does that more or less defeat the purpose, because then they could update/delete the audit table and that makes the value of the logging process a bit dubious?But if I EXECUTE AS [some other superuser - myself, admin, etc], don't I then lose the value of logging who did the update, which I have going into the audit table as current_user ?SO - is there a solution that preserves the sanctity of the AUDIT/LOG table, while still capturing the updating user's ID/NAME ?2) Would you recommend using this same destination table (somehow) for logging DELETES (adding a 2nd trigger that took care of this and sent it to the same audit table), or a different audit destination table for that?Switching some of this type of security/maintenance to the RDBMS instead of having it ALL on the application layer is new thinking to me and all pointers are appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-02 : 18:03:24
|
| 1. I would lockdown the audit table. But what permissions/roles do the developers have?2. I would log updates and deletes to the same table, same trigger even. When inserted is empty, you know it was a delete for an update/delete trigger. Update will cause data to be in both inserted and deleted. I would probably have a column in the audit table to specify which action it was.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
Suggestions on audit/backup (Trigger)