Cascade Update Oracle

SQL, On delete cascade and on update cascade

There is no "on update cascade" in Oracle as far as I know (even in current versions):

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034

Cascade Update Oracle

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.

ON UPDATE CASCADE does not work in Oracle

Oracle supports only ON DELETE CASCADE.

As simple as that.

---

If you want to implement it, use a database trigger. Here's an example.

Sample tables first (in master-detail relationship; source are Scott's EMP and DEPT tables):

SQL> create table t_dept as select deptno, dname from dept;

Table created.

SQL> alter table t_dept add constraint pk_td primary key (deptno);

Table altered.

SQL> create table t_emp as select deptno, empno, ename from emp;

Table created.

SQL> alter table t_emp add constraint pk_te primary key (empno);

Table altered.

SQL> alter table t_emp add constraint fk_te_td foreign key (deptno)
  2    references t_dept (deptno);

Table altered.

Trigger:

SQL> create or replace trigger trg_au_tdept
  2    after update of deptno on t_dept
  3    for each row
  4  begin
  5    update t_emp e set
  6      e.deptno = :new.deptno
  7      where e.deptno = :old.deptno;
  8  end;
  9  /

Trigger created.

Original tables' contents:

SQL> select * from t_dept order by deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> select * from t_emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7782 CLARK
        10       7839 KING
        10       7934 MILLER
        20       7566 JONES
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN

14 rows selected.

Let's update department 10 to 11:

SQL> update t_dept set deptno = 11 where deptno = 10;

1 row updated.

Result:

SQL> select * from t_dept order by deptno;

    DEPTNO DNAME
---------- --------------
        11 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> select * from t_emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        11       7782 CLARK    --> This is the effect of
        11       7839 KING     --> trigger's
        11       7934 MILLER   --> "on update cascade"
        20       7566 JONES
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN

14 rows selected.

SQL>

SQL Keep getting error with ON UPDATE CASCADE

Oracle does not have "ON UPDATE CASCADE". You can manually emulate this behavior by using triggers

---

Related Topics