insert trigger in pl/sql

Question

I cant find the right structure for the following question in pl/sql: need a trigger on “products table” that will check the price before inserting a new product, product price must not exceed 4000$.

CREATE or REPLACE TRIGGER pro 
BEFORE UPDATE OF price 
ON products
FOR EACH ROW 
declare 
pr products.price%type;
BEGIN 
if pr < 4000 then 
INSERT INTO products VALUES (:old.product_ID,:old.price);
end if; 
END;

Please help

Answer 1

Use check constraint instead of trigger:

create table products (price number);

ALTER TABLE PRODUCTS ADD CONSTRAINT check_price CHECK (price < 4000);

Test:

insert into products values (5000)  => ERROR

---

Edit: If you insist on trigger version:

CREATE or REPLACE TRIGGER pro BEFORE insert or UPDATE OF price ON products 
FOR EACH ROW 
BEGIN 
if :new.price > 4000 then 
  raise_application_error(-20101, 'Price exceeds 4000.');
end if; 
END;

Answer 2

The only reason you would bother with a trigger for this instead of a check constraint is to control the error message. And remember that a trigger assumes that the operation is happening, so to stop the operation your tool is to raise an exception.

CREATE OR REPLACE TRIGGER pro 
BEFORE INSERT OR UPDATE
ON products
FOR EACH ROW 
BEGIN 
if :new.price > 4000 then 
     RAISE_APPLICATION_ERROR(-20001,'Price exceeds maximum permitted value') ;
end if; 
END;