OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

Why triggers are evil when they try to get fancy

February 13th, 2006 By Claudiu Ariton

– Added by site admin (Eddie Awad) on 2/28/2006 —
— Start —
Was: How to create ON UPDATE CASCADE constraint


THIS TIP IS VERY DANGEROUS – DO NOT IMPLEMENT THIS IN REAL LIFE – THIS WILL DAMAGE YOUR DATA.
See Thomas Kyte’s comment below.

— End —

create table a (x number primary key);
 
create table b (y number);
 
create table c (y number);
 
ALTER TABLE b ADD CONSTRAINT b_FK FOREIGN KEY (y)
    REFERENCES a (x) on delete cascade deferrable initially deferred
/
 
ALTER TABLE c ADD CONSTRAINT c_FK FOREIGN KEY (y)
    REFERENCES a (x) on delete cascade deferrable initially deferred
/
 

insert into a(x) values (1);
insert into a(x) values (2);
insert into a(x) values (3);
 
insert into b(y) values (1);
insert into b(y) values (2);
insert into c(y) values (1);
insert into c(y) values (2);
 
commit;
select * from a;
select * from b;
select * from c;

create or replace trigger upd_cascade
before update on a
for each row
begin
 
 update b set y=:new.x where y=:old.x;
 update c set y=:new.x where y=:old.x;
 
end;
/

Test it:

update a set x=6 where x=2;
commit;

select * from a;
select * from b;
select * from c;

Best regards,

Claudiu Ariton

8 Responses to “Why triggers are evil when they try to get fancy”

  1. jkstill Says:

    I’m curious why you would want to do this?
    Updating PK is generally considered a bad idea.

    UPDATE CASCADE would have potentially large performance implications, much as DELETE CASCADE does.

  2. Claudiu Ariton Says:

    I am totally agree with you.
    But what are you doing in case of an old bad third party system with wrong meaningful PK (eg: Cities table with city_name as PK).
    You can buy another system but what are the cost?
    This solution is just only a compromis and it is useful for unlucky people from support side.

    Best regards,
    Claudiu Ariton

  3. jkstill Says:

    We are approaching this from different perspectives.

    You are approaching from the perspective of maintaining a legacy app, which sometimes requires us to do things we would rather not do.

    I was thinking in terms of designing a new schema, where there is more flexibility.

  4. Karl Reitschuster Says:

    Hi Claudio,
    if you would give a little intro to your code next time we would better understand the background you want to target. Thanks for posting!

    Karl

  5. austrin Says:

    Hello Claudiu,
    just a hint to the thoughts Tom Kyte had on this topic: http://asktom.oracle.com/~tkyte/update_cascade/index.html
    BR,
    Martin

  6. Claudiu Ariton Says:

    Hello Karl, Jkstill, Austrin

    I’ll keep in mind your opinion in the future.

    My scope was to create an example to demonstrate the deferrable constraint concept, not to create misunderstandings.

    Thank you for understanding,
    Claudiu Ariton

  7. tkyte Says:

    THIS TIP IS VERY DANGEROUS – DO NOT IMPLEMENT THIS IN REAL LIFE – THIS WILL DAMAGE YOUR DATA

    The update cascade someone referenced you to on my page is horribly complex – but only because it has to be. This is a non-trivial operation. This trigger will damage data.

    ops$tkyte@ORA10GR2> create table p (x number primary key);
    Table created.

    ops$tkyte@ORA10GR2> create table c (y references p deferrable initially deferred);
    Table created.

    ops$tkyte@ORA10GR2> insert into p(x) values (1);
    1 row created.

    ops$tkyte@ORA10GR2> insert into p(x) values (2);
    1 row created.

    ops$tkyte@ORA10GR2> insert into c(y) values (1);
    1 row created.

    ops$tkyte@ORA10GR2> insert into c(y) values (2);
    1 row created.

    ops$tkyte@ORA10GR2> commit;
    Commit complete.

    ops$tkyte@ORA10GR2> create or replace trigger upd_cascade
    2 before update on p
    3 for each row
    4 begin
    5 dbms_output.put_line( ‘turning ‘ || :old.x || ‘ into ‘ || :new.x );
    6 update c set y=:new.x where y=:old.x;
    7 end;
    8 /
    Trigger created.

    ops$tkyte@ORA10GR2> select * from p;

    X
    ———-
    1
    2

    ops$tkyte@ORA10GR2> select * from c;

    Y
    ———-
    1
    2

    ops$tkyte@ORA10GR2> update p set x = x+1;
    turning 1 into 2
    turning 2 into 3

    2 rows updated.

    ops$tkyte@ORA10GR2> select * from p;

    X
    ———-
    2
    3

    ops$tkyte@ORA10GR2> select * from c;

    Y
    ———-
    3
    3

    ops$tkyte@ORA10GR2> commit;

    Commit complete.

    The trigger is very dependent on the order the rows in P accidently get processed in!!!! It is totally non-deterministic and corrupts data!!! See how both Y’s in C end up being 3??? Equally possible would be for the “right” answer to happen if the rows in P are processed differently. And if there are more than two rows being updated – ugh – think about the implications of multi-row updates

    I strongly encourage you to consider withdrawing this tip from publication – or to turn it into a tip that shows why triggers are evil when they try to get fancy

  8. Eddie Awad Says:

    Thanks Tom. I have changed the title and added your warning, as well as a direct link to your comment, at the top of this post.

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question