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.