Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
409 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 1000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

722 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