Re: update_cascade utility at govt.us.oracle.com
Date: 1997/11/13
Message-ID: <34735aeb.19862410_at_newshost>#1/1
On Thu, 13 Nov 1997 11:22:50 -0600, c.a.bellmore_at_lilly.com wrote:
>I've downloaded this very useful utility from the above site. Works just
>fine if there are no other triggers in place, but I have an 'on insert,
>before' trigger on a table which uses a sequence to populate a primary key
>using nextval from sys.dual. The Oracle version is 7.3.2.3.0.
>
>This utility conflicts with the insert trigger populating the pk and
>appears to fire it again when updating. Has anyone got a work around
>using this utility, or any suggestions of how to populate a pk without
>using an insert trigger and not doing it on the client.
Here's how you can avoid this. Basically, the way this update cascade package works is:
you update parent row(s)
- a trigger UNDOES the update but remembers the values you would like changed
- the after trigger 'clones' the parent records (inserts them) THIS IS THE PROBLEM
- the after trigger updates the child to point to the new parents
- the after trigger removes the old parent records that are no longer needed
So, how can you make your BEFORE INSERT/FOR EACH ROW trigger not fire (or at least, not assign a new PK value to the parent)..... You can use the same flag I use to prevent recursion in my own update cascade package. When I begin, I set a flag "inTrigger" to TRUE. This flag is always in a package named:
- lowercase u
- tablename
- lowercase p
So, for a table named EMP, the package variable would be "uEMPp".inTrigger. Try this example and see how it works for you. The first update will FAIL (before we recode the trigger) since we re-asign the primary key on all inserts (eg: the 'cloning' of the parent record doesn't work right). The second update, after the recode of the trigger, will succeed.
drop table c;
drop table p;
drop sequence p_seq;
create table p ( x int primary key );
create sequence p_seq;
create trigger p_bifer
before insert on p
for each row
begin
select p_seq.nextval into :new.x from dual;
end;
/
create table c ( a int primary key,
x int references p(x) );
exec update_cascade.on_table('p');
insert into p values (null);
insert into p values (null);
insert into c select rownum+1, x from p;
select * from p;
select * from c;
commit;
update p set x = 100 where x =1;
create or replace trigger p_bifer
before insert on p
for each row
begin
if ( not "uPp".inTrigger ) then
select p_seq.nextval into :new.x from dual;
end if;
end;
/
update p set x = 100 where x =1;
select * from p;
select * from c;
commit;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 13 1997 - 00:00:00 CET