Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger After Update - Values from inserted table Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 1:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1412644
Posted Monday, January 28, 2013 2:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1412647
Posted Monday, January 28, 2013 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1412653
Posted Tuesday, January 29, 2013 1:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1412810
Posted Tuesday, January 29, 2013 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1413053
Posted Tuesday, January 29, 2013 9:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1413124
Posted Tuesday, January 29, 2013 9:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1413129
Posted Tuesday, January 29, 2013 9:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1413144
Posted Tuesday, January 29, 2013 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1413148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse