Change inserted value with trigger
17,113
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;
![hepifish](https://i.stack.imgur.com/eF38N.jpg?s=256&g=1)
Author by
hepifish
Updated on July 07, 2022Comments
-
hepifish almost 2 years
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;
-
hepifish over 10 yearsthank you very much! I've edited the code and everything goes smoothly. Lifesaver.
-
Panu Haaramo over 5 yearsI needed to use
:=