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/