Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73

    Unanswered: Before/After Truncate trigger

    I have tables
    Code:
    CREATE TABLE A (ID NUMBER CONSTRAINT pk_a PRIMARY KEY);
    
    CREATE TABLE B (ID NUMBER CONSTRAINT pk_b PRIMARY KEY, pid NUMBER);
    
    ALTER TABLE b ADD CONSTRAINT fk_b_pid FOREIGN KEY (pid) REFERENCES A(ID);
    Two triggers:
    Code:
    CREATE OR REPLACE TRIGGER tr_bt_a
      BEFORE TRUNCATE ON GG.SCHEMA
    BEGIN
      do_sth ('ALTER TABLE b DISABLE CONSTRAINT fk_b_pid');
    END;
    /
    
    CREATE OR REPLACE TRIGGER tr_at_a
      AFTER TRUNCATE ON GG.SCHEMA
    BEGIN
      do_sth ('ALTER TABLE b ENABLE CONSTRAINT fk_b_pid');
    END;
    /
    And proc
    Code:
    CREATE OR REPLACE PROCEDURE do_sth (
      p_command VARCHAR2
    )
    AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      EXECUTE IMMEDIATE p_command;
      COMMIT;
    END;
    /
    Now, if I try to truncate table A I get
    Code:
    SQL> truncate table a;
    truncate table a
                   *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00054: resource busy and acquire with NOWAIT specified
    ORA-06512: at "GG.DO_STH", line 8
    ORA-06512: at line 2
    If I disable trigger TR_AT_A everything is ok (obviously except fk_b_pid is disabled). Where's the problem?

    Thanks,
    Grzegorz

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The reason you are getting the ORA-00054 is because Oracle is still bussy trying to enable the constraint doing the properly validations it needs to do in order to enable it (it checks if the constraint has been violated, -- so I assume it will have to check all the records on it therefore). Also, I would avoid a trigger like that if I were you. The statement itself commits the changes before/after it execution (hence it's a DDL you are submitting).

Posting Permissions

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