Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Unanswered: how to control cascading updates and deletes

    Hi
    i have oracle sql developer
    i have two related tables and i want to control the cascade update and delete how?

    thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SQL Developer is a tool which might, eventually, help you to accomplish what you'd like to. In other words, you can do the same thing using SQL*Plus, TOAD, etc.; perhaps you should have mentioned Oracle database version instead, but OK, never mind.

    Cascade delete can be done by creating a referential integrity using the ON DELETE CASCADE option. Here's an example; first, let's create two tables:
    Code:
    SQL> CREATE TABLE t_master
      2    (m_id NUMBER PRIMARY KEY,
      3     m_name VARCHAR2(20)
      4    );
    
    Table created.
    
    SQL>
    SQL> CREATE TABLE t_detail
      2    (d_id NUMBER PRIMARY KEY,
      3     m_id NUMBER CONSTRAINT fk_md REFERENCES t_master ON DELETE CASCADE,     --> this here is interesting
      4     d_name VARCHAR2(20)
      5    );
    
    Table created.
    Now we'll insert some records in there:
    Code:
    SQL> INSERT ALL
      2    INTO t_master (m_id, m_name) VALUES (100, 'Littlefoot')
      3    INTO t_master (m_id, m_name) VALUES (101, 'Seco')
      4    INTO t_detail (d_id, m_id, d_name) VALUES (1, 100, 'nx8220')
      5    INTO t_detail (d_id, m_id, d_name) VALUES (2, 100, 'nx6125')
      6    INTO t_detail (d_id, m_id, d_name) VALUES (3, 101, 'nc800')
      7    SELECT * FROM dual;
    
    5 rows created.
    OK; let's delete master record - referential integrity should take care about deleting detail records as well.
    Code:
    SQL> DELETE FROM t_master WHERE m_id = 100;
    
    1 row deleted.
    
    SQL>
    SQL> SELECT * FROM t_master;
    
          M_ID M_NAME
    ---------- --------------------
           101 Seco
    
    SQL>
    SQL> SELECT * FROM t_Detail;
    
          D_ID       M_ID D_NAME
    ---------- ---------- --------------------
             3        101 nc800
    
    SQL>
    As we expected, there are no M_ID = 100 records in detail table.

    However, things that work quite well regarding deletes won't work with updates; as far as I can tell, there's no "cascade update" in Oracle. Perhaps 11g offers it, but I wouldn't know anything about it.

    But, what we CAN do is to make some effort and create a trigger which will do the job for us. Whenever we update master table, corresponding records in detail table will also be updated:
    Code:
    SQL> CREATE OR REPLACE TRIGGER trg_cas_upd
      2    BEFORE UPDATE ON T_MASTER
      3    FOR EACH ROW
      4  BEGIN
      5    UPDATE T_DETAIL SET
      6      m_id = :NEW.m_id
      7      WHERE m_id = :OLD.m_id;
      8  END;
      9  /
    
    Trigger created.
    
    SQL> update t_master set m_id = 200 where m_id = 100;
    
    1 row updated.
    
    SQL> select * From t_master;
    
          M_ID M_NAME
    ---------- --------------------
           200 Littlefoot
           101 Seco
    
    SQL> select * From t_detail;
    
          D_ID       M_ID D_NAME
    ---------- ---------- --------------------
             1        200 nx8220
             2        200 nx6125
             3        101 nc800
    
    SQL>
    Last edited by Littlefoot; 08-15-07 at 13:18.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •