I am trying to get a trigger on the table PACKS that set a min Value in INT Field - PRICE when inserting a new PACK - for example '333'. for insert calling a Procedure new pack. thats the trigger:
CREATE OR REPLACE TRIGGER pack_min_price
BEFORE
INSERT
ON PACKS
FOR EACH ROW
BEGIN
IF new.PRICE < 333 THEN
:new.PRICE := :new.PRICE + 333;
END IF;
END;
and thats the PROCEDURE:
create or replace PROCEDURE newPack(
cntr IN PACKS.COUNTRY%TYPE,
trns IN PACKS.TRANSPORT%TYPE,
htl IN PACKS.HOTEL%TYPE,
extr IN PACKS.HOTELEXTRAS%TYPE,
othextr IN PACKS.OTHEREXTRAS%TYPE,
strdt IN PACKS.STARTDATE%TYPE,
enddt IN PACKS.ENDDATE%TYPE,
prc IN PACKS.PRICE%TYPE)
IS
BEGIN
INSERT INTO PACKS
(COUNTRY,TRANSPORT,HOTEL,HOTELEXTRAS,OTHEREXTRAS,STARTDATE,ENDDATE,PRICE)
VALUES
(cntr,trns,htl,extr,othextr,strdt,enddt,prc);
COMMIT;
END;
the error i get when i try to compile the trigger -
Compilation failed, line 2 (10:12:41) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00201: identifier 'NEW.PRICE' must be declaredCompilation failed, line 2 (10:12:41) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PL/SQL: Statement ignored
and when i start the procedure -
ORA-04098: trigger 'PROJECT160.PACK_MIN_PRICE' is invalid and failed re-validation
without the Trigger the Procedure is working perfectly fine. Can you help? thx
You are missing the : on the new instance in the IF statement.
IF :new.PRICE < 333 THEN
:new.PRICE := :new.PRICE + 333;
END IF;