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.