CODEWITHSUNDEEP
sqlplsqloracle11g
Kenny Randolph
Kenny Randolph

Reputation: 11

SQL statement ignored and ORA-00922 in Oracle11g

I am getting two errors for my trigger and haven't been able to figure out where the problem is, any help is much appreciated :) The trigger should fire on the update of ListPrice column. The trigger should not allow a new list price to be less than StandardCost + 20%. This trigger should allow any price increase.IT should correctly discard changes if the price is too low or allow the update if the price is not too low.

First creating this table:

CREATE TABLE Product (
ProductID NUMBER NOT NULL PRIMARY KEY,
ListPrice NUMBER NOT NULL,
StandardCost NUMBER NOT NULL,
ProductDesc varchar2(20) NOT NULL
);

Then inserting this data into the table:

INSERT INTO product VALUES(1, 20,10,'A');
INSERT INTO product VALUES(2, 21,12,'B');
INSERT INTO product VALUES(3, 22,14,'C');
INSERT INTO product VALUES(4, 23,16,'D');
INSERT INTO product VALUES(5, 40,19,'E');
INSERT INTO product VALUES(6, 22,10,'F');
INSERT INTO product VALUES(7, 25,21,'G');
INSERT INTO product VALUES(8, 29,22,'H');
INSERT INTO product VALUES(9, 25,23,'I');
INSERT INTO product VALUES(10, 30,25,'J');

Here is the code for the trigger where I am getting the error:

CREATE OR REPLACE TRIGGER Product_Price_Check
BEFORE UPDATE OF listprice ON product
FOR EACH ROW
DECLARE
sCost NUMBER(10,5);
BEGIN
sCost := (:old.standardcost + (:old.standardcost*0.2));
IF((:new.listprice) > (:old.listprice))
THEN
SET :old.listprice := :new.listprice;
END IF;
IF (:new.listprice < (sCost))
THEN
RAISE_APPLICATION_ERROR(-20101,'cannot update as price is less');
END IF;
END;
/

These are the error codes:

Error(6,1): PL/SQL: SQL Statement ignored
Error(6,5): PL/SQL:bORA-00922: missing or invalid option

Upvotes: 1

Views: 83

Answers (1)

Arif Sher Khan
Arif Sher Khan

Reputation: 585

I understand that don't need to user SET keyword in the code. Along with that we cannot assign value to old. I have commented that part in below code. Further, you can completely comment first if block as you only need to raise error if your conditions are not satisfied otherwise it will allow the update.

create or replace trigger product_price_check before
    update of listprice on product
    for each row
declare
    scost   number(
        10,5
    );
begin
    scost   :=:old.standardcost + (:old.standardcost * 0.2 );

    --if :new.listprice > :old.listprice then
        -- set :old.listprice   := :new.listprice;
    --end if;

    if (:new.listprice < ( scost ) ) then
        raise_application_error(
            -20101,
            'cannot update as price is less'
        );
    end if;

end;
/

Upvotes: 1

Related Questions