CODEWITHSUNDEEP
sqloracle-database
Nik
Nik

Reputation: 25

Trigger with SELECT from same table

I want to build a trigger that takes the minimum price for a book and is triggered if the new price is 5 times the minimum value. I get an error for my SELECT statement. I think it is because I am selecting from the same table the trigger was fired.

CREATE OR REPLACE TRIGGER check
BEFORE INSERT OR UPDATE ON book_type
FOR EACH ROW

DECLARE 
newPrice number;
lowestPrice number;
topPrice number;

BEGIN
newPrice := NEW.price;
lowestPrice := (SELECT MIN(price) FROM book_type);
topPrice := (5 * lowestPrice); 

IF (topPrice < newPrice) THEN
  dbms_output.put_line('Error. Price too high.');
END IF;
END;
/

Upvotes: 1

Views: 4050

Answers (1)

DCookie
DCookie

Reputation: 43523

First, you can't assign a value that way. You'd need something like:

select min(price) into lowestPrice from book_type;

However, the next problem you'll see is a Mutating Table error (ORA-4091), because you're attempting a select from the same table the trigger is defined for. In other words, the trigger is defined on table book_type, and you're trying to select from table book_type in your trigger. Oracle does not allow this on statement triggers.

Finally, dbms_output isn't terribly useful in a trigger, because there's no terminal to display your output to. Generally, you'd raise an exception instead.

Upvotes: 2

Related Questions