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
February 13th, 2006 at 11:32 am
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.
February 13th, 2006 at 12:01 pm
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
February 13th, 2006 at 12:05 pm
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.
February 13th, 2006 at 10:26 pm
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
February 13th, 2006 at 11:06 pm
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
February 13th, 2006 at 11:38 pm
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
February 28th, 2006 at 9:16 am
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
February 28th, 2006 at 10:23 am
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.