PL/SQL Trigger with specific variable from update

Question

I'm trying to trigger an update after an update.

My Tables:

CREATE TABLE STUDENT (
  STUDENT_ID          INTEGER     PRIMARY KEY,
  NAME                CHAR(40)    NOT NULL,
  SURNAME             CHAR(40)    NOT NULL,
  TOKEN               CHAR(5)     NOT NULL,
  STUDYCOURSE         CHAR(20)    NOT NULL,
  NOTE                VARCHAR(255)    NULL,
  WARNING_ID          INTEGER     REFERENCES MAHNUNG (ID),
  BLOCKED             CHAR(1)     CHECK (Gesperrt IN('J', 'N'));

create table RENTAL (
  RENTAL_ID           INTEGER     PRIMARY KEY,
  RENTALDATE          DATE        DEFAULT SYSDATE,
  RENTALDURATION      INTEGER     NOT NULL,
  OVERDRAWN           CHAR(1)     CHECK (OVERDRAWN IN('Y', 'N'));

My Trigger:

CREATE OR REPLACE TRIGGER OVERDRAWN_RETURN
AFTER UPDATE OF OVERDRAWN ON RENTAL
  REFERENCING OLD AS OLD AND NEW AS NEW

BEGIN
  IF :NEW.OVERDRAWN := 'Y' THEN
  UPDATE STUDENT SET WARNING_ID = WARNING_ID+1
  WHERE STUDENT.STUDENT_ID = RENTAL.STUDENT_ID;
  END IF;
END OVERDRAWN_RETURN;
/

What I'm basically trying to do is increment the WARNING_ID when OVERDRAWN is updated to 'Y' (yes). Error report -

ORA-04079: invalid trigger specification
04079. 00000 -  "invalid trigger specification"
*Cause:    The create TRIGGER statement is invalid.
*Action:   Check the statement for correct syntax.

What did I do wrong?

btw, you should use VARCHAR2, not VARCHAR or CHAR. Also, your caps lock is on ;) — William Robertson
– William Robertson, Commented Feb 10, 2017 at 17:07

Prashant Mishra · Accepted Answer · 2017-02-10 13:55:36Z

1

you were using assignment operator(:=) in IF statement.

  CREATE OR REPLACE TRIGGER OVERDRAWN_RETURN
    AFTER UPDATE OF OVERDRAWN ON RENTAL
      REFERENCING OLD AS OLD AND NEW AS NEW

    BEGIN
      IF :NEW.OVERDRAWN = 'Y' THEN
      UPDATE STUDENT SET WARNING_ID = WARNING_ID+1
      WHERE STUDENT.STUDENT_ID = RENTAL.STUDENT_ID;
      END IF;
    END OVERDRAWN_RETURN;
    /

answered Feb 10, 2017 at 13:55

Prashant Mishra

6479 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2017-02-10 14:32:27Z

1

As well as the use of := instead of =for comparison that @Prashant pointed out, there are some other issues. The actual error you see is because of the REFERENCING line, which should not have the AND.

But you can't use new and old for statement triggers, so you need to make it a FOR EACH ROW trigger too:

CREATE OR REPLACE TRIGGER OVERDRAWN_RETURN
AFTER UPDATE OF OVERDRAWN ON RENTAL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

BEGIN
  IF :NEW.OVERDRAWN = 'Y' THEN
    UPDATE STUDENT SET WARNING_ID = WARNING_ID+1
    WHERE STUDENT_ID = :NEW.STUDENT_ID;
  END IF;
END OVERDRAWN_RETURN;
/

You were referring to RENTAL.STUDENT_ID, but that is out of scope for the SQL statement. So I've changed that to refer to the current row, with :NEW.STUDENT_ID. But either way, your RENTAL table doesn't have a STUDENT_ID column, but you may have just omitted that when editing the definition for posting.

answered Feb 10, 2017 at 14:32

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

2 Comments

HandsomeJane Over a year ago

Yes, sorry, I inserted STUDENT_ID via ALTER TABLE command and forgot to insert it directly into my table. So is :NEW.STUDENT_ID referring to the updated row of RENTAL above?

Alex Poole Over a year ago

@HandsomeJane - yes, referring to :NEW.xxx means the new value of xxx in the affected row the trigger fired against. Read more. If the update statement had changed STUDENT_ID then :OLD.STUDENT_ID would be the old value last set in that row in the table, and :NEW.STUDENT_ID would be the new value supplied in the update statement, and what the table row will contain after the update completes. In this case you probably aren't expecting it to change.

Collectives™ on Stack Overflow

PL/SQL Trigger with specific variable from update

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related