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>. alaries 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 el 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.