Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: before delete triggers...mutating error

    hi i need to make a trigger that will fire whenever i delete a Customer,
    but i keep getting mutating table error....can someone tell mewhat's wrong with my code?
    My tables are bellow
    Thanks

    CREATE TABLE CUSTOMER (
    CUSTOMER_ID NUMBER(6) not null,
    HOTEL_ID NUMBER(4) not null,
    SSN NUMBER(9) not null,
    LAST_NAME VARCHAR2(15)not null,
    FIRST_NAME VARCHAR2(15) not null,
    CONSTRAINT PK_CUSTOMER_ID PRIMARY KEY (CUSTOMER_ID),
    CONSTRAINT FK_HOTEL_ID FOREIGN KEY (HOTEL_ID) REFERENCES HOTEL
    );

    CREATE TABLE CUSTOMER_NOTE (
    NOTE_DATE DATE not null,
    CUSTOMER_ID NUMBER(6) not null,
    NOTE_COMMENTS VARCHAR(4000) not null,
    CONSTRAINT PK_CUSTOMER_NOTE PRIMARY KEY (CUSTOMER_ID,NOTE_DATE),
    CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER
    );

    CREATE TABLE MYORDER (
    MYORDER_NUMBER NUMBER(9) not null,
    MYORDER_DATE DATE not null,
    SERVICE_ID CHAR(6) not null,
    EMPLOYEE_ID CHAR(5) not null,
    CUSTOMER_ID NUMBER(6) not null,
    ACTUAL_CHARGE NUMBER(9,2) not null,
    CONSTRAINT PK_MYORDER PRIMARY KEY (MYORDER_NUMBER,MYORDER_DATE),
    CONSTRAINT FK_SERVICE_ID FOREIGN KEY (SERVICE_ID) REFERENCES SERVICE,
    CONSTRAINT FK_EMPLOYEE_ID_MYORDER FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE,
    CONSTRAINT FK_CUSTOMER_ID_MYORDER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER
    );


    CREATE TABLE MYLISTING (
    MYLISTING_NUMBER NUMBER(9) not null,
    CUSTOMER_ID NUMBER(6) not null,
    EMPLOYEE_ID CHAR(5) not null,
    BILL_CODE CHAR(8)not null,
    MYLISTING_DATE DATE not null,
    CONSTRAINT PK_MYLISTING_NUMBER PRIMARY KEY (MYLISTING_NUMBER),
    CONSTRAINT FK_CUSTOMER_ID_MYLISTING FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER,
    CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE,
    CONSTRAINT FK_BILL_CODE FOREIGN KEY (BILL_CODE) REFERENCES BILL
    );


    CREATE OR REPLACE TRIGGER DeleteCUSTOMER
    BEFORE DELETE ON CUSTOMER
    FOR EACH ROW

    -- Before a row is deleted from CUSTOMER or the primary key
    -- check for dependent

    DECLARE
    Dummy1 INTEGER; -- used for cursor fetch below
    Dummy2 INTEGER; -- used for cursor fetch below
    Dummy3 INTEGER; -- used for cursor fetch below
    -- Cursor used to check for dependent foreign key values.
    CURSOR Dummy_cursor1 (Dn NUMBER) IS
    SELECT CUSTOMER_ID FROM CUSTOMER_NOTE WHERE CUSTOMER_ID = Dn;

    CURSOR Dummy_cursor2 (Dn NUMBER) IS
    SELECT CUSTOMER_ID FROM MYLISTING WHERE CUSTOMER_ID = Dn;

    CURSOR Dummy_cursor3 (Dn NUMBER) IS
    SELECT CUSTOMER_ID FROM MYORDER WHERE CUSTOMER_ID = Dn;

    BEGIN
    OPEN Dummy_cursor1 (:old.CUSTOMER_ID);
    FETCH Dummy_cursor1 INTO Dummy1;
    IF Dummy_cursor1%FOUND THEN
    DELETE FROM CUSTOMER_NOTE
    WHERE CUSTOMER_NOTE.CUSTOMER_ID = :old.CUSTOMER_ID;
    DELETE FROM CUSTOMER
    WHERE CUSTOMER.CUSTOMER_ID = :old.CUSTOMER_ID;
    CLOSE Dummy_cursor1;

    ELSE
    DELETE FROM CUSTOMER
    WHERE CUSTOMER.CUSTOMER_ID = :old.CUSTOMER_ID;

    END IF;
    CLOSE Dummy_cursor1;

    OPEN Dummy_cursor2 (:old.CUSTOMER_ID);
    FETCH Dummy_cursor2 INTO Dummy2;
    IF Dummy_cursor2%FOUND THEN
    DELETE FROM MYLISTING
    WHERE MYLISTING.CUSTOMER_ID = :old.CUSTOMER_ID;
    DELETE FROM CUSTOMER
    WHERE CUSTOMER.CUSTOMER_ID = :old.CUSTOMER_ID;
    CLOSE Dummy_cursor2;

    ELSE
    DELETE FROM CUSTOMER
    WHERE CUSTOMER.CUSTOMER_ID = :old.CUSTOMER_ID;

    END IF;
    CLOSE Dummy_cursor2;

    OPEN Dummy_cursor3 (:old.CUSTOMER_ID);
    FETCH Dummy_cursor3 INTO Dummy3;
    IF Dummy_cursor3%FOUND THEN
    DELETE FROM MYORDER
    WHERE MYORDER.CUSTOMER_ID = :old.CUSTOMER_ID;
    DELETE FROM CUSTOMER
    WHERE CUSTOMER.CUSTOMER_ID = :old.CUSTOMER_ID;
    CLOSE Dummy_cursor3;
    ELSE
    DELETE FROM CUSTOMER
    WHERE CUSTOMER.CUSTOMER_ID = :old.CUSTOMER_ID;
    END IF;
    CLOSE Dummy_cursor3;

    END;
    /

  2. #2
    Join Date
    Apr 2004
    Posts
    13
    Can you give shorter example...?
    Correct me if I wrong.

  3. #3
    Join Date
    Apr 2004
    Posts
    6

    shorter vers

    well basically i have 1 table with 3 child tables...i wanna have a before delete...so when i delete the parent ..it will delete all the child tables too...
    ....hope this make u clear of what i need

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You shouldn't be trying to delete from "customer" in the trigger. The delete will occur later automatically. Basically you are trying to delete a row in the customer table, which has already got a delete in progress for the same row.

    What you are trying to achieve appears to be better handled by ON DELETE CASCADE option on your foreign keys. These will handle deleting the child rows for you and you can do away with the trigger entirely.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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