tracking old values

[SqlHunter]

I want to have a general after update trigger where it tracks the values which when an update occurs.Is there any way I can track the previous value as well.say one field is changed from 'John' to 'Helen' and Now to get back the old value is there any way ?.something like an audit trail

 

[mrdenny]

If you have not cleared the transaction log then you can use Lumigent SQL Explorer to go through the transaction log and pull the old values.

Other than that, no. You would have to create the audit trail yourself.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dbomrrsm]

see thread thread183-797756 if you want to create an audit trail trigger.

good luck

DBomrrsm

 

[ceyhorn]

We have an audit log table with hundreds of thousands of entries tracking everyone's actions. We are currently working on a way to backtrack to see the state of the file on x date.

 

[SqlHunter]

CREATE TRIGGER Test ON emp_details
after UPDATE
AS
declare @new varchar(13)
declare @old varchar (13)

select @new = (Select emp_id from inserted)
select @old = (select emp_id from deleted)

insert into Audit_details (Old_value,New_value)
(@new,@old)

I want to get the new value and the old value when an update is happend.This is giving some error

 

[SQLSister]

SQL Hunter, your code is only good if only one record is updated at a time. Triggers should always be written to account for multiple record updates. Do a join on inserted and deleted instead. Something like:

insert into Audit_details (Old_value,New_value)
select deleted.emp_id, inserted.emp_id from inserted join deleted on inserted.emp_id = deleted.emp_id

Questions about posting. See faq183-874

 

[SqlHunter]

The above trigger is not working.The syntax everything is fine but is not inserting data into Audit_detail table

 

[SqlHunter]

I guess this should solve my problem.

INSERT INTO Audit_details (Old_value, New_value)
SELECT d.emp_id, i.emp_id
FROM inserted i
INNER JOIN deleted d
ON i.ssn = d.ssn

where ssn is the primary key

 

[ESquared]

For what it's worth, I think it's best to not use real-world data such as SSNs for row or entity identifiers. For one thing, SSNs should be considered sensitive data. For another, they aren't evenly distributed; indexes on real-world data can be inefficient. Last, real-world data can change. There have been cases of duplicate SSNs being assigned or people getting new ones, and if that happens you have a database update mess.

-------------------------------------
A sacrifice is harder when no one knows you've made it.