CODEWITHSUNDEEP
oracle-databasetriggers
user2112730
user2112730

Reputation: 51

Update and Delete Oracle Trigger

Posted a question a couple of days ago and successfully got my trigger to work! But having a few new problems.

I have two tables:

CREATE TABLE  "ASSESSMENT" 
  (     "ASSESSMENT_NAME" VARCHAR2(50) NOT NULL ENABLE, 
        "DEADLINE_DATE" DATE NOT NULL ENABLE, 
         CONSTRAINT "ASSESSMENT_PK" PRIMARY KEY ("ASSESSMENT_NAME") ENABLE
  )

CREATE TABLE  "ASSESSMENT_ANNOUNCEMENT" 
  (    "ASSESSMENT_NAME" VARCHAR2(50) NOT NULL ENABLE, 
        "DEADLINE_DATE" DATE NOT NULL ENABLE, 
        "ATTENTION" VARCHAR2(500) NOT NULL ENABLE, 
        CONSTRAINT "ASSESSMENT_ANNOUNCEMENT_PK" PRIMARY KEY ("ASSESSMENT_NAME")  ENABLE
  )

The trigger I have:

CREATE OR REPLACE TRIGGER  "TEST"
  AFTER INSERT OR UPDATE OR DELETE 
  ON ASSESSMENT
FOR EACH ROW
BEGIN
  IF :new.DEADLINE_DATE >= SYSDATE - 7
  THEN
    INSERT INTO ASSESSMENT_ANNOUNCEMENT(ASSESSMENT_NAME, DEADLINE_DATE ,ATTENTION)
      VALUES(:new.ASSESSMENT_NAME, :new.DEADLINE_DATE, 'DEADLINE IS 7 DAYS OR LESS!');
  END IF;
END;

Insert works correctly across the tables. But, when I update on the ASSESSMENT table, a new row is inserted in the ASSESSMENT_ANNOUNCEMENT table - it is not updated.

A delete from the ASSESSMENT table removes the row from ASSESSMENT table but not the entry from the ASSESSMENT_ANNOUNCEMENT table.

Any help and/or guidance would be fantastic!

Upvotes: 1

Views: 3629

Answers (2)

Jirawat Uttayaya
Jirawat Uttayaya

Reputation: 1302

Just to give you a complete sample

CREATE OR REPLACE TRIGGER  "TEST"
    AFTER INSERT OR UPDATE OR DELETE 
    ON ASSESSMENT
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        IF :new.DEADLINE_DATE >= SYSDATE - 7
        THEN
            INSERT INTO ASSESSMENT_ANNOUNCEMENT(ASSESSMENT_NAME, DEADLINE_DATE ,ATTENTION)
            VALUES(:new.ASSESSMENT_NAME, :new.DEADLINE_DATE, 'DEADLINE IS 7 DAYS OR LESS!');
        END IF;

    ELSIF UPDATING THEN
        UPDATE ASSESSMENT_ANNOUNCEMENT SET 
          ASSESSMENT_NAME=:new.ASSESSMENT_NAME,
          DEADLINE_DATE=:new.DEADLINE_DATE,
          ATTENTION='Deadline Updated'
        WHERE ASSESSMENT_NAME=:old.ASSESSMENT_NAME;

    ELSE
       DELETE ASSESSMENT_ANNOUNCEMENT
         WHERE ASSESSMENT_NAME=:old.ASSESSMENT_NAME;
    END IF; 
END;

Depending on your real business logic and size of PL/SQL code, it may be more clear to create three triggers

CREATE OR REPLACE TRIGGER "TEST_AI_TRG" AFTER INSERT ON ASSESSMENT ...
CREATE OR REPLACE TRIGGER "TEST_AU_TRG" AFTER UPDATE ON ASSESSMENT ...
CREATE OR REPLACE TRIGGER "TEST_AD_TRG" AFTER DELETE ON ASSESSMENT ...

Upvotes: 0

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

If you want to update or delete a row in ASSESSMENT_ANNOUNCEMENT, you should do it explicitly using update or delete statement.
Use the following construction in your trigger: 

IF INSERTING THEN
  -- actions for inserting
ELSIF UPDATING THEN
  -- actions for updating
ELSE
  -- actions for deleting
END IF;

Upvotes: 1

Related Questions