By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Trigger to reflect changes

P: 1
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.
Jul 17 '06 #1
Share this Question
Share on Google+
1 Reply


P: 37
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;
Jul 27 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.