Change inserted value with trigger

Question

I've just started to learn SQL a few weeks ago and I'm trying to make a trigger which changes the inserted value into 10 if it's smaller than 10. I searched for 4h now and I've found a lot of answers but none was good(for me). I really don't understand where the problem is. Here is the code:

CREATE OR REPLACE TRIGGER NumberOfBooks
BEFORE INSERT
ON Book
FOR EACH ROW
BEGIN 
  IF new.nobook < 10
  THEN
    SET new.nobook = 10;
  END IF;
  END;

Error(8,9): PL/SQL: ORA-00922: missing or invalid option and the set new.nobook = 10 is underlined — hepifish, Nov 16 '13 at 18:00

score 8 · Accepted Answer · edited Apr 26 '19 at 08:11

8

In Oracle's trigger syntax the newly inserted record is referred to by :new, not new (notice the colon). Additionally, SET is a part of an update statement, not a way to set field values - those are done by simple assignments, but note that these are done with := rather than =.
So, your trigger should read:

CREATE OR REPLACE TRIGGER NumberOfBooks
    BEFORE INSERT
    ON book
    FOR EACH ROW
BEGIN
    IF :new.nobook < 10
    THEN
        :new.nobook := 10;
    END IF;
END;

edited Apr 26 '19 at 08:11

kara

3,205
4
20
34

answered Nov 16 '13 at 18:07

Mureinik

297,002
52
306
350

thank you very much! I've edited the code and everything goes smoothly. Lifesaver. – hepifish Nov 16 '13 at 18:13
I needed to use `:=` – Panu Haaramo Dec 05 '18 at 12:49

Change inserted value with trigger

1 Answers1

Linked