Cascade Update Oracle

I have the following tables: CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50), contact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (su

Solution 1:

Normally, you'd structure your data model such that primary keys are immutable in order to avoid the problem in the first place.

If you absolutely need to allow for cascading updates, you probably want to use Tom Kyte's cascading update script. For each table, this will dynamically generate a package and three triggers (before statement, before row, and after statement) that will implement all the logic needed to cascade the update. This is likely to be vastly more efficient (and more accurate) than anything that a single developer would knock together. Just because of the rather large number of objects that need to be created, though, it's a pretty heavy overhead to maintain.