Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gdeconto
Posting Yak Master
107 Posts |
 Posted - 2008-09-22 : 18:30:55
|
| I have a need to provide audit logs for multiple related tables. This may be used as a means to UNDO database updates.I have created and used audit logs many times so that is not a problem. However, my current problem is a bit more complex since I have to track changes made to multiple tables as part of one operation.say we have this situation:1. there are multiple related tables that need to get updated when a certain action occurs (lets use Delete as in this scenario). i.e. when I delete a record from table A I also need to delete some records from tables B and C and D2. tables B, C and D can be inserted/updated/deleted without affecting table A3. I have an audit log table that contains some information about the action performed (a unique action identifier, some info about the user, a timestamp of some sort, whether the action is add/delete/update, etc)4. I have a copy of each table that may be affected (lets call them history tables). the history table structure has one additional field; a unique identifier that ties back to a record in the audit log table above5. I have a trigger on each table that may be affected; when the trigger is activated (by add/update/delete), I create an entry in the audit log table, grab the unique identifier for that entry, and copy affected records from the original table to the history table (including the unique identifier)6. I now delete a record from table Awhen I delete a record from table A, I also have to delete entries from tables B, C and D. Because the triggers track the change to each table individually, this means that I now have four unique identifiers in the audit log table and no way to know that these four deletions are related.Any ideas on how to allow me to associate these four entries int the audit log table?Note that I COULD do this via code rather than triggers, however doing so would not trap any changes that occurred via direct database manipulation (ie transact-sql).any help appreciated. thx |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 02:58:13
|
| Do you have any audit columns like updatedby,modifiedby,...in B,C & D? |
 |
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2008-09-23 : 11:15:21
|
| no. I was thinking about centralizing info of that sort in the audit log table. this was sort of what I was thinking about:audit log table:CREATE TABLE [dbo].[audit_log]( [id] [bigint] IDENTITY(1,1) NOT NULL, [action_type] [char](1) NOT NULL, [updated] [datetime] NOT NULL CONSTRAINT [DF_audit_log_updated] DEFAULT (getdate()), [updated_by] [varchar](50) NOT NULL ...) ON [PRIMARY]then in each of the tables (A,B,C,D) I would have a field named audit_log_id that would refer to the audit log table entry.of course, in a trigger there doesn't seem to be a way for me to grab info about who updated the table. I think I might have to modify the sprocs that update the tables to include some sort of userid field that would only be used by the trigger to record info about who did the modification. again, that doesnt get me any info about who did the modification if the tables are modified directly (ie outside the sprocs)any ideas? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 11:22:28
|
quote:
Originally posted by gdeconto
no. I was thinking about centralizing info of that sort in the audit log table. this was sort of what I was thinking about:audit log table:CREATE TABLE [dbo].[audit_log]( [id] [bigint] IDENTITY(1,1) NOT NULL, [action_type] [char](1) NOT NULL, [updated] [datetime] NOT NULL CONSTRAINT [DF_audit_log_updated] DEFAULT (getdate()), [updated_by] [varchar](50) NOT NULL ...) ON [PRIMARY]then in each of the tables (A,B,C,D) I would have a field named audit_log_id that would refer to the audit log table entry.of course, in a trigger there doesn't seem to be a way for me to grab info about who updated the table. I think I might have to modify the sprocs that update the tables to include some sort of userid field that would only be used by the trigger to record info about who did the modification. again, that doesnt get me any info about who did the modification if the tables are modified directly (ie outside the sprocs)any ideas?
Nope. you can use SUSER_SNAME() function to get info of who caused update and GETDATE() to get the datetime info |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2008-09-23 : 13:01:40
|
| thanks. unfortunate that there is no way to identify the group of updates/deletions/insertions.wonder if there is any internal id value attached to the current sql transaction, similar to scope_identity? if there isnt, there should be.that would have been one way; we could use the transaction id value to associate the changes. |
|
|
|
|
|
|
|
|
relating audit log entries for multiple tables