Oracle Triggers

[Sethington]

Hey guys, I have a trigger that inserts an ID into 6 tables but I'm
having trouble making the code more concise.

create or replace trigger WELL_ID_TRIGGER
after insert on WELLS
for each row
begin

insert into WELL_MAIN(WELL_ID),
select :new.well_id from dual;

insert into WELL_CONSTRUCTION(WELL_ID),
select :new.well_id from dual;

insert into WELL_GEOLOG(WELL_ID),
select :new.well_id from dual;

insert into WELL_LOCATION27(WELL_ID),
select :new.well_id from dual;

insert into WELL_LOCATION83(WELL_ID),
select :new.well_id from dual;

insert into WELL_ORIG_WATER_LEVEL(WELL_ID),
select :new.well_id from dual;

end;

[John Martinson]

As far as I know, if you are going to insert something into six tables, you will need six insert statements. But as I look at your code it looks like you are calling a constructor method and creating 6 new and separate well_id objects. Although this might not be the case based on what your constructor method does in the well_id object.

 

You might want to consider calling the constructor only once, saving that into a named object and then inserting that named object into your six tables. This should save processing, memory and improve the speed of your execution.  Again that is only assuming that the constructor you are calling gives a different object every time it is called.
 

John G. Martinson

http://jgmartinson.blogspot.com/
 

[Sethington]

Basically what the code is doing is just updating the 6 tables with a
new well Id chich are all the number.

The thing that I was really intrested in was if this code could be done
simpler. I gess that is as simple as it gets so thanks for your reply.

[Tejay Cardon]

I believe you can just insert values without the select statements.  Like so:

  create or replace trigger WELL_ID_TRIGGER
  after insert on WELLS
  for each row
  begin

           insert into WELL_MAIN(WELL_ID)

           values       :new.well_id;

           insert into WELL_CONSTRUCTION(WELL_ID)
           values       :new.well_id;         

           insert into WELL_GEOLOG(WELL_ID)
           values       : new.well_id;

           insert into WELL_LOCATION27(WELL_ID)
           values       :new.well_id;

           insert into WELL_LOCATION83(WELL_ID)
           values       :new.well_id;

           insert into WELL_ORIG_WATER_LEVEL(WELL_ID)

           values       :new.well_id;

  end;