Tracking Old value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tracking Old value

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-11 : 16:46:37

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-11 : 16:56:09

You would use both the deleted and inserted tables that are available in triggers. Deleted contains the previous values, inserted contains the new values.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-11 : 17:37:12

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-11 : 18:18:32

Providing us with the error would help.

You need to test for the column being updated. SQL Server Books Online has examples in the CREATE TRIGGER topic.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-11 : 18:30:02

I made the above code working so its fine for single update at a time
I know that triggers should always be written to account for multiple record updates. will a join on inserted and deleted help something like this

CODE
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

Here the insertion is not happening..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-11 : 18:34:49

You can't join on emp_id between the two tables because they are different due to the UPDATE. Is emp_id the PK? It is considered bad design to update a PK.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-11 : 18:48:59

No its not the primary key.so how is the trigger written to account for multiple record updates where the old and new values are captured

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-11 : 18:52:04

I would need to see the PK in order to help. You would use the PK in the insert with a join.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-11 : 19:00:55

primary key is SSN.so is that going to be

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-11 : 19:08:20

Yes that's correct, although I prefer it look like this:

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

It's much easier to read.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-11 : 19:48:21

Thanks tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources