Should I include code for an existing table trigger in an 'instead of insert' view trigger for that table?

Hi -

In an Oracle database, I have a table (SAMPLES) with a simple trigger that fires before insert to add a unique, sequential number to the table's primary key (note, code from OEM):

BEGIN  
 SELECT animal_be.S_11123_1_SAMPLES.nextval  
 INTO :new.SAMPLEID  
 FROM dual;  
END;

I have created a view using a join query betwen table SAMPLES and table COLLECTIONS.  The view works fine and I have also created an "instead of update" trigger on the view to update SAMPLES (the other table is not updated from the view) which also works fine.  

I want to create an "instead of insert" trigger on the view and am unsure of what to do about the SAMPLES table trigger that gets the nextval into the primary key.  The SAMPLES table may be updated from the view OR at the table level.  If I add the code for the table primary key trigger to the 'instead of update' trigger, will the trigger at the table level still fire and rerun code for the table primary key?  Would I end up with two records that are duplicates except for the primary key value (one from the view trigger  and one from the table trigger)?  Or would the table level trigger not fire because it is 'before insert' and the view trigger actually caused the record to be inserted?

Thanks,
Jackie Senderling
aksealifeIS ManagerAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Keep the trigger on the base table.  It should fire just fine.
0
 
anumosesCommented:
Either have before insert on the table or instead of insert. You cannot have both.
Insert on the table is better and update on the view is ok
0
 
slightwv (䄆 Netminder) Commented:
>>You cannot have both.


Sure you can.  See below.  You just need to be aware of the firing order.
drop table tab1 purge;
create table tab1(col1 number);

create or replace trigger tab1_trig
before insert on tab1
for each row
begin
	:new.col1 := :new.col1+1;
end;
/

show errors


create or replace view tab1_view as
select * from tab1;

create or replace trigger tab1_view_trig
instead of insert on tab1_view
for each row
begin
	insert into tab1 values(:new.col1 + 2);
end;
/

show errors

insert into tab1 values(1);
insert into tab1_view values(1);
select * from tab1;

Open in new window

0
 
aksealifeIS ManagerAuthor Commented:
Thanks for a simple answer to a confusing issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.