January 28, 2013 at 1:50 pm
Hi,
I am trying to create a table with references to the updates of a specific table.
So simply -
TableA.KeyField
TableA.Description
When TableA.KeyField is updated - I want to store the before and after values in TableB
TableB.KeyField
TableB.Before
TableB.After
So I have created a trigger on TableA ( AFTER UPDATE ) but from the description itself...
"AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully"
When I check TableB - it contains the same in Before and After fields.
How can I capture the before values ?
my trigger.....
ALTER TRIGGER [dbo].[trgGenMasterUpdate]
ON [dbo].[GenMaster]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into GenMasterUpdates (GlCode, Action, Before, After )
select INSERTED.GlCode, 'UPDATE', GenMaster.Description, INSERTED.Description
from INSERTED join GenMaster on INSERTED.GlCode = GenMaster.GlCode
END
Thanks in advance
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
January 28, 2013 at 2:00 pm
I have managed to answer my own question - through a little digging -
Using the DELETED table as the before data.
New Trigger;
ALTER TRIGGER [dbo].[trgGenMasterUpdate]
ON [dbo].[GenMaster]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into GenMasterUpdates (GlCode, Action, Before, After )
select INSERTED.GlCode, 'UPDATE', DELETED.Description, INSERTED.Description
from INSERTED join GenMaster on INSERTED.GlCode = GenMaster.GlCode
join DELETED on INSERTED.GlCode = DELETED.GlCode
END
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
January 28, 2013 at 2:15 pm
SteveEClarke (1/28/2013)
I have managed to answer my own question - through a little digging -Using the DELETED table as the before data.
New Trigger;
ALTER TRIGGER [dbo].[trgGenMasterUpdate]
ON [dbo].[GenMaster]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into GenMasterUpdates (GlCode, Action, Before, After )
select INSERTED.GlCode, 'UPDATE', DELETED.Description, INSERTED.Description
from INSERTED join GenMaster on INSERTED.GlCode = GenMaster.GlCode
join DELETED on INSERTED.GlCode = DELETED.GlCode
END
Assuming you have some way of knowing when the update is made (like a datetime column for UpdateDate) you really don't need the After data. The after data will be in the next audit row (assuming you order them), if there is no previous row the next value will be what is currently in the table. This will cut your auditing storage in half. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 1:43 am
Sean Lange (1/28/2013)
SteveEClarke (1/28/2013)
I have managed to answer my own question - through a little digging -Using the DELETED table as the before data.
New Trigger;
ALTER TRIGGER [dbo].[trgGenMasterUpdate]
ON [dbo].[GenMaster]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into GenMasterUpdates (GlCode, Action, Before, After )
select INSERTED.GlCode, 'UPDATE', DELETED.Description, INSERTED.Description
from INSERTED join GenMaster on INSERTED.GlCode = GenMaster.GlCode
join DELETED on INSERTED.GlCode = DELETED.GlCode
END
Assuming you have some way of knowing when the update is made (like a datetime column for UpdateDate) you really don't need the After data. The after data will be in the next audit row (assuming you order them), if there is no previous row the next value will be what is currently in the table. This will cut your auditing storage in half. 🙂
Yes - I agree - the example I posted was a very simple one just to get me and others thinking -
The final table has the following columns ;
DATE
TIME
COLUMN_Updated
BEFORE
AFTER
OPERATOR
I have since put this script live and is working exactly as I had hoped.
I appreciate your input though.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
January 29, 2013 at 7:44 am
SteveEClarke (1/29/2013)
Sean Lange (1/28/2013)
SteveEClarke (1/28/2013)
I have managed to answer my own question - through a little digging -Using the DELETED table as the before data.
New Trigger;
ALTER TRIGGER [dbo].[trgGenMasterUpdate]
ON [dbo].[GenMaster]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into GenMasterUpdates (GlCode, Action, Before, After )
select INSERTED.GlCode, 'UPDATE', DELETED.Description, INSERTED.Description
from INSERTED join GenMaster on INSERTED.GlCode = GenMaster.GlCode
join DELETED on INSERTED.GlCode = DELETED.GlCode
END
Assuming you have some way of knowing when the update is made (like a datetime column for UpdateDate) you really don't need the After data. The after data will be in the next audit row (assuming you order them), if there is no previous row the next value will be what is currently in the table. This will cut your auditing storage in half. 🙂
Yes - I agree - the example I posted was a very simple one just to get me and others thinking -
The final table has the following columns ;
DATE
TIME
COLUMN_Updated
BEFORE
AFTER
OPERATOR
I have since put this script live and is working exactly as I had hoped.
I appreciate your input though.
Just my 2¢ again but I would not recommend using an EAV style of audit table. What I mean by that is you have the value which now will have to be stored as nvarchar regardless of the original datatype. This will cause you challenges at some point. But the bigger challenge is trying to figure out what any given row contained at a given point in time. With the way you are doing this you may quickly get millions of rows in this audit table. Let's say you have 50 columns and you routinely update 40 of them. You will have 40 rows in your audit table for each update, and each row is representing a column in the original table.
Consider this, management walks in and says I know you audit this table now so can you please get me the contents from December 14th 2012 at 8:36am. How are you going to retrieve that data with this style of table?
I find it much easier to just record the whole row on update. Then all you have to do for a point in time is find the most recent rows prior to the requested time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 9:11 am
I see where you are coming from - honestly do appreciate your input.
However, we currently have this style of "audit table" setup from our ERP system, so I am in turn reflecting/repeating what is already in place. I am only concerned in 6 fields that change, which are from an Accounts table - these change under strict conditions, and is just an audit report backing up what has already been requested and passed - this complies with SOX.
From your "point in time" request from the management - I am able to do this, as I am storing the date and time that the record was updated.
I am happy and confident that the solution I have implemented is satisfactory for this project
Thanks again for your input - which I may choose to use on another project.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
January 29, 2013 at 9:15 am
as an aside - I have checked the "Main Audit" table - which contains over 200 fields and have 275,000 records over the 3 years of using the system, which also included a 40,000 item upload in the initial stages - so am happy that the process a) works and b) will not get too out of control.
Thanks again
Regards
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
January 29, 2013 at 9:31 am
Glad it is working for you.
The point in time thing is good for a single column but can be a real pita to show what the entire table looked like at a given point in time. You have to parse all the columns 1 by 1 to make sure you get the value for each column at that point in time. I have had to that very sort of thing for a SOX audit once and it was super ugly and SLOW to retrieve that data.
I feel your pain that your ERP has it already. We can't always design everything. Good luck and post back if you have any issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 9:35 am
Thanks -
Yes unfortunately the ERP system does, Inventory, Customer, Suppliers, but not LEDGER chanegs - which is a blackhole - so I have just replicated that they do and ... well its been signed off by the FD - so I am happy 🙂
I am sure there will be more to do though ....
Am looking forward to my SQL BI in 3 weeks time 🙂 might help my reporting techniques further.
Cheers
Steve
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply