1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to write a delete trigger

Discussion in 'SQL PL/SQL' started by MFriend, Jul 17, 2015.

  1. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi Folks:

    I have two tables:
    1. Bales_Harvested
    2. Bales_Storage

    I use the following trigger (works fine) when inserting a row into Bales_Harvested to copy the row to the Bales_Storage table:
    Code (SQL):
    CREATE OR REPLACE TRIGGER FARM.BALES_HARVESTED_TRIG FOR INSERT ON FARM.BALES_HARVESTED REFERENCING OLD AS "OLD" NEW AS "NEW"
    COMPOUND TRIGGER

    AFTER EACH ROW IS
    BEGIN
    INSERT INTO BALES_STORAGE (BALES_RECEIVED_GAINED, CROP, CUTTING, STACKYARD, DATE_MOVED, HARVEST_ID, BALE_YEAR) VALUES(:NEW.BALES_RECEIVED, :NEW.CROP, :NEW.CUTTING, :NEW.STACKYARD, :NEW.DATE_RECEIVED, :NEW.HARVEST_ID, :NEW.BALE_YEAR);
    END AFTER EACH ROW;

    END;
    /
    What I need to do now is create an on delete trigger that when a row is deleted in the Bales_Harvested table will fire and delete the corresponding row (that was copied previously) out of the Bales_Storage table.
    The Primary key of the Bales_Harvested table is: HARVEST_ID. Harvest ID is the FK in the Bales_Storage table.

    Thanks for any help, I appreciate it.

    Matthew
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It should be something like the following. That said -- I can't test it without creating the tables...

    Code (Text):

    CREATE OR REPLACE TRIGGER bales_harvested_del
    AFTER DELETE
       ON FARM.BALES_HARVESTED
       FOR EACH ROW

    BEGIN
       DELETE FROM bales_storage
       WHERE  harvest_id = old.harvest_id;
    END;
     
  3. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Matthew for the help... I will give it a try.

    Matt
     
  4. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi Folks:
    I have tried using the following trigger. It inserts ok, updates, ok, but gives a mutating table error.
    The Code I am using is:
    Code (SQL):

    CREATE OR REPLACE TRIGGER FARM.HARVEST_DATA_TRG
    AFTER DELETE OR INSERT OR UPDATE
    ON FARM.BALES_HARVESTED
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
      -- your code here
      -- (Trigger template "Default" could not be loaded.
      IF INSERTING THEN
      INSERT INTO BALES_STORAGE (BALES_RECEIVED_GAINED, CROP, CUTTING, STACKYARD, DATE_MOVED, HARVEST_ID, BALE_YEAR) VALUES(:NEW.BALES_RECEIVED, :NEW.CROP, :NEW.CUTTING, :NEW.STACKYARD, :NEW.DATE_RECEIVED, :NEW.HARVEST_ID, :NEW.BALE_YEAR);
       
      ELSIF UPDATING THEN
      UPDATE BALES_STORAGE
      SET  BALES_RECEIVED_GAINED= :NEW.BALES_RECEIVED,
      CROP= :NEW.CROP,
      CUTTING= :NEW.CUTTING,
      STACKYARD= :NEW.STACKYARD,
      DATE_MOVED= :NEW.DATE_RECEIVED,
      HARVEST_ID= :NEW.HARVEST_ID,
      BALE_YEAR= :NEW.BALE_YEAR
      WHERE  BALES_STORAGE.harvest_id = :OLD.harvest_id;
       
      ELSIF DELETING THEN
      DELETE FROM BALES_STORAGE
      WHERE  harvest_id = :OLD.harvest_id;
       
      END IF;
     
    END;
    /
     
    Attached is a screen pic of the error. I tried :new.harvest_id; as well as old.harvest_id; and I tried using the table name before it (i.e. WHERE BALES_STORAGE = :new.harvest_id; )

    Thanks for any help...

    Matthew
     

    Attached Files:

    Last edited: Jul 20, 2015
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    My guess would be that something in BALES_STORAGE (like another trigger) references BALES_HARVESTED. When an action in BALES_HARVESTED activates the trigger shown in your post -- it tries to update BALES_STORAGE. In turn, that DML action tries to perform an action in turn against BALES_HARVESTED, causing a mutating table error.
     
    MFriend likes this.
  6. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi OCPREP:

    You are correct. The two talbes were foreign keyed. On the FK, it had cascade delete set. Once I removed the cascade the trigger began working normally. Thanks for looking at this form me.

    Matthew