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
404 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
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 76

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

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.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

810 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