P11-Creació de disparadors
En esta práctica, creará disparadores de sentencia y de fila. También creará procedimientos que se llamarán desde los disparadores.
1) Las filas de la tabla JOBS almacenan los salarios mínimos y máximos permitidos para los distintos valores de JOB_ID. Le piden que escriba un código para garantizar que el salario de los empleados esté dentro del rango permitido por su tipo de trabajo, para operaciones de inserción y actualización.
a) Cree un procedimiento denominado CHECK_SALARY, de la siguiente forma: i) El procedimiento acepta dos parámetros, uno para la cadena del identificador de trabajo del empleado y el otro para el salario. ii) El procedimiento utiliza el identificador de trabajo para determinar el salario mínimo y máximo para el trabajo especificado. iii) Si el parámetro del salario, mínimo y máximo incluidos, no está dentro del rango de salarios, aparecerá una excepción de aplicación con el mensaje “Invalid salary <sal>. Salaries for job <jobid> must be between <min> and <max>.” Sustituya los distintos elementos del mensaje por los valores que proporcionan los parámetros y las variables rellenados con consultas. Guarde el archivo. b) Cree un disparador denominado CHECK_SALARY_TRG en la tabla EMPLOYEES que arranque ante una operación INSERT o UPDATE en cada fila: i) El disparador debe llamar al procedimiento CHECK_SALARY para ejecutar la lógica de negocio. ii) El disparador debe transferir el nuevo identificador de trabajo y salario a los parámetros de procedimiento. 2) Pruebe el disparador CHECK_SAL_TRG utilizando los siguientes casos: a) Utilice el procedimiento EMP_PKG.ADD_EMPLOYEE para agregar a la empleada Eleanor Beh al departamento 30. ¿Qué sucede? ¿Por qué? b) Actualice el salario del empleado 115 a 2.000 dólares. En otra operación de actualización, cambie el identificador de trabajo del empleado a HR_REP. ¿Qué sucede en cada caso? c) Actualice el salario del empleado 115 a 2.800 dólares. ¿Qué sucede? 3) Actualice el disparador CHECK_SALARY_TRG para que arranque sólo cuando los valores del identificador de trabajo o el salario hayan cambiado en realidad. a) Implante la regla de negocio utilizando una cláusula WHEN para comprobar si los valores JOB_ID o SALARY han cambiado. Nota: asegúrese de que la condición maneja NULL en los valores de OLD.column_name si se realiza una operación INSERT; si no es así, la operación de inserción fallará. b) Compruebe el disparador ejecutando el procedimiento EMP_PKG.ADD_EMPLOYEE con los siguientes valores de parámetros: - p_first_name: 'Eleanor' - p_last name: 'Beh' - p_Email: 'EBEH' - p_Job: 'IT_PROG' - p_Sal: 5000 c) Actualice a los empleados con un trabajo IT_PROG incrementando su salario en 2.000 dólares. ¿Qué sucede? d) Actualice a 9.000 dólares el salario de Eleanor Beh. Indicación: utilice una sentencia UPDATE con una subconsulta en la cláusula WHERE. ¿Qué sucede? e) Cambie el trabajo de Eleanor Beh a ST_MAN utilizando otra sentencia UPDATE con una subconsulta. ¿Qué sucede? 4) Se le pide que evite que se suprima a los empleados durante las horas laborables. a) Escriba un disparador de sentencia denominado DELETE_EMP_TRG en la tabla EMPLOYEES para evitar que las filas se supriman durante horas laborables entre semana, es decir, de las 9:00 a.m. a las 6:00 p.m. b) Intente suprimir los empleados con JOB_ID SA_REP que no estén asignados a un departamento. Indicación: empleado Grant con identificador 178.
