Trigger to reflect changes

[vios]

Hi,

I have created a trigger as below to update a table called vnd_trail to show what sort of operation(update/delete) happened to the name column. My problem is, I want to have another column to show the changes that took place. For example name field: maxim systems has been changed to maximus system. How can I do that in my trigger below?

CREATE TRIGGER vnd_chk
AFTER UPDATE OR DELETE ON vnd
FOR EACH ROW
DECLARE
oper varchar2(8);
ruid number;
name varchar2(64);
name_change varchar2(64);

BEGIN
if updating then
oper:='update';
end if;

if deleting then
oper:='delete';
end if;

/*store previous value into vnd_trail*/
ruid:=:old.ruid;
name:=:old.name;
insert into vnd_trail
values(ruid,name,oper,sysdate,name_change);
END;

thank you for any advice.

[masdi2t]

Hi,
let's say that you have another column named 'desc',
you need a little modification on your trigger

CREATE TRIGGER vnd_chk
AFTER UPDATE OR DELETE ON vnd
FOR EACH ROW
DECLARE
oper varchar2(8);
ruid number;
name varchar2(64);
name_change varchar2(64);

/* additional */
desc varchar2(255);

BEGIN
if updating then
oper:='update';

/* additional */
desc := concat('value: ', old.name, ' was changed to: ', new.name);
end if;

if deleting then
oper:='delete';

/* additional */
desc := concat('value: ', old.name, ' was deleted');
end if;

/*store previous value into vnd_trail*/
ruid:=ld.ruid;
name:=ld.name;
insert into vnd_trail
values(ruid,name,oper,sysdate,name_change, /* additional */desc);
END;