Solved

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

Posted on 2010-11-29
4
407 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:aksealife
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 34233789
Keep the trigger on the base table.  It should fire just fine.
0
 
LVL 6

Expert Comment

by:anumoses
ID: 34233806
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34233892
>>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
 

Author Closing Comment

by:aksealife
ID: 34242420
Thanks for a simple answer to a confusing issue.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question