Home » SQL & PL/SQL » SQL & PL/SQL » how to simplify the following trigger code (10g)
how to simplify the following trigger code [message #382861] Sun, 25 January 2009 22:59 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
hi,
i've written the following code that is as per the requirements but how can that code even be reduced
either using INSERT ALL option or other way::
create table src(seq number ,len number);
 
create table dest(mod varchar2(1),out varchar2(1),
sequence number , length number);
 
create or replace trigger trig1
after insert or update or delete
of seq,len
on src
referencing new as new old as old
for each row
declare
vseq src.seq%type;
vlen src.len%type;
vmod varchar2(1);
vout varchar2(1);
begin
 
if updating ('seq') or inserting then
vseq:=:new.seq;
vlen:=:new.len;
vmod:='i';
vout:='n';
insert into dest(mod,out,sequence,length) values(vmod,vout,vseq,vlen);
end if;
 
if updating('seq') or deleting then
vseq:=:old.seq;
vlen:=:old.len;
vmod:='d';
vout:='y';
insert into dest(mod,out,sequence,length) values(vmod,vout,vseq,vlen);
end if;
 
if updating('len') then
vseq:=:new.seq;
vlen:=:new.len;
vmod:='u';
vout:='n';
insert into dest(mod,out,sequence,length) values(vmod,vout,vseq,vlen);
end if;
end;

SQL> insert into src values(1,2);
 
1 row created.
 
SQL> select * from src;
 
       SEQ        LEN
---------- ----------
         1          2
 
SQL> select * from dest;
 
M O   SEQUENCE     LENGTH
- - ---------- ----------
i n          1          2
 
SQL> update src set seq=2;
 
1 row updated.
 
SQL> select * from dest;
 
M O   SEQUENCE     LENGTH
- - ---------- ----------
i n          1          2
i n          2          2
d y          1          2
 
SQL> update src set len=3;
 
1 row updated.
 
SQL> select * from dest;
 
M O   SEQUENCE     LENGTH
- - ---------- ----------
i n          1          2
i n          2          2
d y          1          2
u n          2          3
 
SQL> delete from src;
 
1 row deleted.
 
SQL> select * from dest;
 
M O   SEQUENCE     LENGTH
- - ---------- ----------
i n          1          2
i n          2          2
d y          1          2
u n          2          3
d y          2          3
 
SQL> 


thanks
Re: how to simplify the following trigger code [message #382875 is a reply to message #382861] Mon, 26 January 2009 00:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As INSERT statement is common to all cases, you could move it out of the IF-THEN-ELSE; something like
begin
  if updating('seq') then
     if inserting then
        vseq:=:new.seq;
        vlen:=:new.len;
        vmod:='i';
        vout:='n';
     elsif deleting then
        vseq:=:old.seq;
        vlen:=:old.len;
        vmod:='d';
        vout:='y';
     end if;
  elsif updating('len') then
     vseq:=:new.seq;
     vlen:=:new.len;
     vmod:='u';
     vout:='n';
  end if;

  insert into dest(mod,out,sequence,length) values(vmod,vout,vseq,vlen);
end;
Re: how to simplify the following trigger code [message #382903 is a reply to message #382861] Mon, 26 January 2009 03:55 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Another possibility is to remove all inrtermidiate variables:

begin
   if updating ('seq') or inserting then
      insert into dest(mod,out,sequence,length)
         values('i','n',:new.seq,:new.len);
   end if;
 
   if updating('seq') or deleting then
      insert into dest(mod,out,sequence,length)
        values('d','y',:old.seq,:old.len);
   end if;
 
  if updating('len') then
      insert into dest(mod,out,sequence,length)
         values('u','n',:new.seq,:new.len);
   end if;
end;


Make your choice.

HTH.
Re: how to simplify the following trigger code [message #383431 is a reply to message #382903] Wed, 28 January 2009 20:31 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Just my opinion,

I like the second one better. My brain does not have to think as hard about wher things are comming from.

Kevin
Previous Topic: Driving Table
Next Topic: How to bifurcate the data from single column
Goto Forum:
  


Current Time: Fri Dec 09 21:28:14 CST 2016

Total time taken to generate the page: 0.10751 seconds