Problem with triggers in SQL 6.5

Problem with triggers in SQL 6.5

Post by jackh.. » Wed, 13 Oct 1999 04:00:00



How can I create an update trigger that add
records in a logtable, the following fields are
required:

Date, USER, Table, Field, Oldvalue, Newvalue

The fields Date, User and Table are easy to use
but i don't see how to retreive the Field, the
oldvalue and the newvalue.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Problem with triggers in SQL 6.5

Post by Stephen Robinso » Wed, 13 Oct 1999 04:00:00


Jack,

When you have an update trigger you have access to two logical table
(existing for the length of the trigger) deleted and inserted an update is
in-effect a delete followed by an insert so you can get your oldvalue and
newvalue from these linking the two or your key fields.

The insert and delete triggers only have the inserted or deleted tables
respectivly

You can also see which of the fields have been modified by using the
updated(fieldname) function.  here is an example

create table X (
field1 varchar(2),
field2 varchar(2)
)
go

create trigger X1 on X for update as
if update(field2)
begin
select i.field1,i.field2 'After',d.field2 'Before' from inserted i,deleted d
where d.field1 = i.field1
end
go
insert into X values ('1','2')
go
update X set field2 = '3'
go

--
I hope this was of use.

Stephen Robinson SQL MVP


> How can I create an update trigger that add
> records in a logtable, the following fields are
> required:

> Date, USER, Table, Field, Oldvalue, Newvalue

> The fields Date, User and Table are easy to use
> but i don't see how to retreive the Field, the
> oldvalue and the newvalue.

> Sent via Deja.com http://www.deja.com/
> Before you buy.