Log changes to data

[robsuttonjr]

I need to log changes to all fields for a particular table. Example, I have a table with Name, PO_Number, Unit_Cost, Comments, when values are updated/inserted/deleted I want to insert the previous and new value in a log table with the datetime & user_id for each field. Any thoughts on how to do this?

It would look something like:
Field_name Previous_Value New_Value User Date
Name Roberta Roger sa 10/10/03
Unit_Cost 12.00 30.04 jj 10/10/03
PO_Number 34334 223433 ss 10/10/03
Unit_Cost 30.04 40.00 kk 10/20/03

Any ideas on how to best do this without huge cost?

 

[vganeshbabu]

Try using triggers for that table. u will be able to get the old values and the new values, u can update them to a new table..

Ganesh

 

[robsuttonjr]

I am trying to create a trigger right now that inserts the previous and new value into a log table but want the most efficient method. Here is what I am attempting with no luck:

CREATE TRIGGER tr_Log_Changes
ON dbo.Map_Detail
FOR INSERT, UPDATE, DELETE

AS

IF (SELECT Map_Lock FROM INSERTED) = 1

IF (SELECT Item_id FROM Deleted) != (SELECT Item_id FROM INSERTED)
INSERT Change_Log
(Field_Name, Previous_Value, New_Value, Change_User_id, Change_Date)
(SELECT 'Item_id', Item_id FROM Inserted) (SELECT Item_Id, GETDATE(), USER_ID() FROM Deleted)

GO

 

[robsuttonjr]

This is the only thing I can figure but if someone has a better way let me know. I need to do the following hard coding for every field.


CREATE TRIGGER tr_Log_Changes
ON dbo.Map_Detail
FOR INSERT, UPDATE, DELETE

AS

IF (SELECT Lock_Map FROM INSERTED) = 1

IF (SELECT Item_id FROM Deleted) != (SELECT Item_id FROM INSERTED)
INSERT Change_Log
(Field_Name, New_Value, Change_User_id, Change_Date)
SELECT 'Item_id', Item_id, GETDATE(), USER_ID() FROM INSERTED

UPDATE Change_log
SET Previous_Value = (SELECT Item_id FROM Deleted)
WHERE Map_Detail_Log_Key = @@IDENTITY

 

[vganeshbabu]

this should be ok..

Ganesh

 

[SQLSister]

uh, don;t you need a field to identify which specific record was changed? Do you have some record ID field in your base table?