'PL/SQL How to set value before Raise_application_error
I'm creating a trigger to set off if prixHeure < 0. Im new to triggers.
My problem is that Id like to do two things if prixHeure < 0 but I cant make it work. To set the value where it was going to be negative to 0 and then do a raise_applicaton_error() to stop and print a message.
Ive made either work but not both at the same time. Is it possible?
Also Im getting an error PLS-00103: Symbole "UPDATE" found when I spool my whole table but if I run my code, trigger first and then the tests, I dont get the error. Thanks in advance.
Here is my code:
--TRIGGER2
CREATE OR REPLACE TRIGGER restrictionTarifHoraire
BEFORE UPDATE ON RESSOURCESPROJET FOR EACH ROW
--DECLARE taux_horaire RESSOURCESPROJET.prixHeure%TYPE;
BEGIN
IF :NEW.prixHeure > (:old.prixHeure * 1.5)
THEN
:New.prixHeure := :old.prixHeure ;
END IF;
IF :NEW.prixHeure < 0
THEN
:New.prixheure := 0 ;
END IF;
IF :old.prixHeure = 0
THEN
RAISE_APPLICATION_ERROR(-20100, 'PrixHeure < 0, le nouveau prixHoraire est de 0 pour cet employé') ;
END IF;
END;
--TEST2.1
UPDATE RESSOURCESPROJET
SET prixHeure = 48
WHERE ressourcesprojet.idEmploye=5465;
--TEST2.2
UPDATE RESSOURCESPROJET
SET prixHeure = -1
WHERE ressourcesprojet.idEmploye=5465;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
