CODEWITHSUNDEEP
sqloracle-database
Shakira Weloveyou
Shakira Weloveyou

Reputation: 3

Code an SQL Trigger that raises an exception

I have to code a SQL trigger that raises an error if the price of an item is set to a value that is more than four times the value of the least expensive item. Name of table is itemType and name of price column is price. This is what I tried but I can't seem to get it working.

CREATE OR REPLACE TRIGGER harekrishna
BEFORE INSERT ON itemType 
REFERENCING NEW AS newrow
FOR EACH ROW
DECLARE
hPrice NUMBER;
BEGIN
SELECT price INTO hPrice 
  FROM itemType
  WHERE price = :newrow.price; 
IF hPrice > 4 * MIN(price)
  raise_application_error(-20000, (‘Price of item’ |:newrow.price |‘greater than 4 times the least expensive item’); 
END IF;

Upvotes: 0

Views: 121

Answers (1)

Kacper
Kacper

Reputation: 4818

You have wrong quote characters, missing THEN and missing END in last line. Try that one and let me know if you have more erros.

CREATE OR REPLACE TRIGGER harekrishna
    BEFORE INSERT ON itemType 
    REFERENCING NEW AS newrow
    FOR EACH ROW
    DECLARE
    hPrice NUMBER;
    BEGIN
    SELECT min(price) INTO hPrice 
      FROM itemType; 
    IF :newrow.price > 4 * hPrice  THEN
      raise_application_error(-20000, 'Price of item' ||:newrow.price ||'greater than 4 times the least expensive item'); 
    END IF;
    END;

Also logic of this select make no sense so I changed it to get min price and in if just use :newrow.

Upvotes: 1

Related Questions