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
403 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
  • 2
4 Comments
 
LVL 76

Accepted Solution

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

Expert Comment

by:anumoses
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
Thanks for a simple answer to a confusing issue.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now