Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: BEFORE ROW triggers and row state

    So, I learned in a class about 3 years ago to expect the following
    Code:
    SQL> create table p (pk number primary key);
    
    Table created.
    
    SQL> create table c (fk number references p(pk));
    
    Table created.
    
    SQL> create or replace trigger t_insrt
      2  before insert on p
      3  for each row
      4  begin
      5   insert into c values (:new.pk);
      6  end;
      7  /
    
    Trigger created.
    
    SQL> insert into p values (1);
    insert into p values (1)
                *
    ERROR at line 1:
    ORA-02291: integrity constraint (FORBESC.SYS_C00169150) violated - parent key
    not found
    ORA-06512: at "FORBESC.T_INSRT", line 2
    ORA-04088: error during execution of trigger 'FORBESC.T_INSRT'
    and so it led me to think that replicating ON MODIFY PARENT - MODIFY CHILD functionality wouldn't work in a BEFORE ROW trigger, but it does

    Code:
    SQL> drop trigger t_insrt;
    
    Trigger dropped.
    
    SQL> create or replace trigger p_updt
      2  before update on p
      3  for each row
      4  begin
      5   update c
      6   set fk = :new.pk
      7   where fk = :old.pk;
      8  end;
      9  /
    
    Trigger created.
    
    SQL> insert into p values (1);
    
    1 row created.
    
    SQL> insert into c values (1);
    
    1 row created.
    
    SQL> select * from c;
    
            FK
    ----------
             1
    
    SQL> update p
      2  set pk = 2
      3  where pk = 1;
    
    1 row updated.
    
    SQL> select * from c;
    
            FK
    ----------
             2
    Why would the first scenario fail while the second succeeds? The update seems prone to a parent record also not existing, at least not by the BEFORE ROW trigger.

    ---=Chuck

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Interesting! I can't imagine why that works, but my feeling is that it is some unintended consequence of the way Oracle currently implements the trigger, and not something that you should rely on - i.e. I would carry on assuming you can't do that in general, and would not do it!

Posting Permissions

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