SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger After Update - Values from inserted table


Trigger After Update - Values from inserted table

Author
Message
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 457
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.
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 457
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63705 Visits: 17974
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.

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)
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 457
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63705 Visits: 17974
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.

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)
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 457
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.
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 457
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63705 Visits: 17974
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.

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)
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 457
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search