I'm trying to do an exercise, but I've been having a hard time moving forward:
We want to create the following triggers:
- A trigger called TRIG_EMPLOYEES that jumps when inserting or updating in the N_EMPLOYEES table so that it does not let us insert or update if one of the following cases occurs:
o The registration date must be less than the system date.
o Sex can only have value H or M, in uppercase
o The province must be CIUDAD REAL, CUENCA, GUADALAJARA, TOLEDO or ALBACETE, in capital letters
An exception (RAISEE_APPLICATION_ERROR) will be thrown, whereby the record is not inserted and displays the proper message.
I leave you the code I have:
-- EJERCICIO 4
CREATE OR REPLACE TRIGGER TRIG_EMPLEADOS
BEFORE INSERT OR UPDATE ON N_EMPLEADOS
FOR EACH ROW
BEGIN
IF :NEW.FECHA_ALTA > SYSDATE THEN
RAISE_APLICATION_ERROR(-20001, 'LA FECHA DEBE SER SUPERIOR AL DIA ACTUAL');
END IF;
IF UPPER(:NEW.SEXO) <> 'H' OR UPPER(:NEW.SEXO) <> 'M' THEN
RAISE_APLICATION_ERROR(-20002, 'EL SEXO DEBE SER H - M EN MAYUSCULAS');
END IF;
IF UPPER(:NEW.PROVINCIA) <> 'CIUDAD REAL' OR
UPPER(:NEW.PROVINCIA) <> 'CUENCA' OR
UPPER(:NEW.PROVINCIA) <> 'GUADALAJARA' OR
UPPER(:NEW.PROVINCIA) <> 'TOLEDO' OR
UPPER(:NEW.PROVINCIA) <> 'ALBACETE' THEN
RAISE_APLICATION_ERROR(-20003, 'LA PROVINCIA DEBE ESTAR EN CASTILLA LA MANCHA');
END IF;
END;
The thing is that the trigger compiles for me, but it complains:
Trigger TRIG_EMPLEADOS compilado
LINE/COL ERROR
--------- -------------------------------------------------------------
3/9 PLS-00201: identifier 'RAISE_APLICATION_ERROR' must be declared
3/9 PL/SQL: Statement ignored
7/9 PLS-00201: identifier 'RAISE_APLICATION_ERROR' must be declared
7/9 PL/SQL: Statement ignored
15/9 PLS-00201: identifier 'RAISE_APLICATION_ERROR' must be declared
15/9 PL/SQL: Statement ignored
Errores: comprobar log de compilador
It is complaining that
RAISE_APLICATION_ERROR
it is not declared, so you have to add a sectionDECLARE
of one at the beginningEXCEPTION
and call them in the cases you want: