Trigger

Question

Hello,

From this trigger taht i created, before to insert a data in the table A_CLIENT, it suppose to show me the message of the raise_application_error. Can you help me please.
Thank you.

create or replace trigger TR_VERIFIER_AGE
before insert or update on A_CLIENT
for each row
declare
V_AGE number;
begin
select AGE_CLI into V_AGE
from A_CLIENT
where AGE_CLI = :new.AGE_CLI;
if(V_AGE < 10) then
raise_application_error(-4455,'L''age de la persone est trop faible');
end if;
end TR_VERIFIER_AGE;
/

Answer 1

The row is not added yet as the trigger is executed before insert.
As a result, the select statement raises no_data_found exception.

You have the value saved in the variable :new.AGE_CLI

So, the code can be as follows :

CREATE TABLE A_CLIENT(
AGE_CLI INTEGER PRIMARY KEY
);

CREATE OR REPLACE TRIGGER TR_VERIFIER_AGE
BEFORE INSERT OR UPDATE ON A_CLIENT
FOR EACH row
DECLARE
V_AGE NUMBER;
BEGIN
IF(:new.AGE_CLI < 10) THEN
Raise_Application_error(-20000,'L''age de la persone est trop faible');
END IF;
END TR_VERIFIER_AGE;
/

INSERT INTO A_CLIENT values(3);

--Output :
--Error starting at line : 17 in command -
--INSERT INTO A_CLIENT values(3)
--Error report -
--SQL Error: ORA-20000: L'age de la persone est trop faible
--ORA-06512: at "HR.TR_VERIFIER_AGE", line 5
--ORA-04088: error during execution of trigger 'HR.TR_VERIFIER_AGE'