DAI-C8-EC: Triggers, exercicis proposats

Objectius

Unitat Didàctica: UD 5. Triggers

Després de fer i entendre els exercicis comentats a classe, l'alumne ja està en disposició de programar triggers i resoldre exercicis concrets a partir d'un enunciat.

Desenvolupament

Realitza els següents exercicis:

1. Construir un disparador de base de datos que permita auditar las operaciones de inserción o borrado de datos que se realicen en la tabla emple según las siguientes especificaciones:

• En primer lugar se creará desde SQL*Plus la tabla auditaremple con la columna col1 VARCHAR2(200).
• Cuando se produzca cualquier manipulación se insertará una fila en dicha tabla que contendrá:
• Fecha y hora
• Número de empleado
• Apellido
• La operación de actualización INSERCIÓN o BORRADO

2. Escribir un trigger de base de datos un que permita auditar las modificaciones en la tabla empleados insertado en la tabla auditaremple los siguientes datos:

• Fecha y hora
• Número de empleado
• Apellido
• La operación de actualización: MODIFICACIÓN.
• El valor anterior y el valor nuevo de cada columna modificada. (solo las columnas modificadas)

3. Escribir un disparador de base de datos que haga fallar cualquier operación de modificación del apellido o del número de un empleado, o que suponga una subida de sueldo superior al 10%.

4. Suponiendo que disponemos de la vista

CREATE VIEW DEPARTAM AS
SELECT DEPART.DEPT_NO, DNOM, LOC, COUNT(EMP_NO) TOT_EMPLE  
FROM EMPLE, DEPART
WHERE EMPLE.DEPT_NO (+) = DEPART.DEPT_NO
GROUP BY DEPART.DEPT_NO, DNOM, LOC;

Construir un disparador que permita realizar operaciones de actualización en la tabla depart a partir de la vista dptos, de forma similar al ejemplo del trigger t_ges_emplead. Se contemplarán las siguientes operaciones:

• Insertar departamento.
• Borrar departamento.
• Modificar la localidad de un departamento.

solucions

<toggledisplay> 1. Construir un disparador de base de datos que permita auditar las operaciones de inserción o borrado de datos que se realicen en la tabla emple según las siguientes especificaciones:

• En primer lugar se creará desde SQL*Plus la tabla auditaremple con la columna col1 VARCHAR2(200).
• Cuando se produzca cualquier manipulación se insertará una fila en dicha tabla que contendrá:
• Fecha y hora
• Número de empleado
• Apellido
• La operación de actualización INSERCIÓN o BORRADO

CREATE TABLE auditaremple (camp1 VARCHAR2(255));

CREATE OR REPLACE TRIGGER auditar_act_emp
   BEFORE INSERT OR DELETE
   ON EMPLE
   FOR EACH ROW
BEGIN
   IF DELETING THEN
     INSERT INTO AUDITAREMPLE
 VALUES(TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') 
 || :OLD.EMP_NO|| '*' || :OLD.COGNOM || '* ELIMINAT ');
   ELSIF INSERTING THEN
     INSERT INTO AUDITAREMPLE
   	 VALUES(TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') 
 || :NEW.EMP_NO || '*' || :NEW.COGNOM ||'* INSERIT ');
   END IF;
END;
/

2. Escribir un trigger de base de datos un que permita auditar las modificaciones en la tabla empleados insertado en la tabla auditaremple los siguientes datos:

• Fecha y hora
• Número de empleado
• Apellido
• La operación de actualización: MODIFICACIÓN.
• El valor anterior y el valor nuevo de cada columna modificada. (solo las columnas modificadas)

CREATE OR REPLACE TRIGGER audit_modif
  	BEFORE UPDATE  ON EMPLE
  	FOR EACH ROW 
DECLARE
	v_cad_inser auditaremple.camp1%TYPE;
BEGIN
v_cad_inser := TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') ||:OLD.EMP_NO ||'* MODIFICACIÓ *';

    	IF UPDATING ('EMP_NO') THEN		
  	v_cad_inser := v_cad_inser ||:OLD.EMP_NO|| '*'|| :NEW.EMP_NO;
	END IF;

	IF UPDATING ('COGNOM') THEN		
    	v_cad_inser := v_cad_inser ||:OLD.COGNOM|| '*'||:NEW.COGNOM;
	END IF;

	IF UPDATING ('OFICI') THEN		
	   	v_cad_inser := v_cad_inser ||:OLD.OFICI|| '*'||:NEW.OFICI;
	END IF;

	IF UPDATING ('DIR') THEN		
	v_cad_inser := v_cad_inser ||:OLD.DIR|| '*'||:NEW.DIR;
	END IF;

	IF UPDATING ('DATA_ALT') THEN		
	v_cad_inser := v_cad_inser || :OLD.DATA_ALT||:NEW.DATA_ALT;
	END IF;

	IF UPDATING ('SALARI') THEN		
		v_cad_inser := v_cad_inser || :OLD.SALARI || '*'||:NEW.SALARI;
	END IF;

	IF UPDATING ('COMISSIO') THEN		
	    v_cad_inser := v_cad_inser ||:OLD.COMISSIO || '*'||:NEW.COMISSIO;
	END IF;

	IF UPDATING ('DEPT_NO') THEN		
	    v_cad_inser := v_cad_inser ||:OLD.DEPT_NO|| '*'||:NEW.DEPT_NO;
	END IF;
	
	INSERT INTO AUDITAREMPLE VALUES(v_cad_inser);
END;
/

3. Escribir un disparador de base de datos que haga fallar cualquier operación de modificación del apellido o del número de un empleado, o que suponga una subida de sueldo superior al 10%.

CREATE OR REPLACE TRIGGER errada_modif
	BEFORE UPDATE OF cognom, emp_no, salari
	ON emple
	FOR EACH ROW
BEGIN
	IF UPDATING('emp_no') OR UPDATING('cognom') OR (UPDATING ('salari')  AND :new.salari>:old.salari*1.1) THEN
	   RAISE_APPLICATION_ERROR (-20001,'Err. Modificació no permesa');
	END IF;
END;
/

/* NOTA: teniendo en cuenta los eventos que producirán el disparo del trigger la condición se puede resumir:  
IF NOT UPDATING ('salario') OR :new.salario > :old.salario*1.1
THEN ...
*/

4. Suponiendo que disponemos de la vista

CREATE VIEW DEPARTAM AS
SELECT DEPART.DEPT_NO, DNOM, LOC, COUNT(EMP_NO) TOT_EMPLE  
FROM EMPLE, DEPART
WHERE EMPLE.DEPT_NO (+) = DEPART.DEPT_NO
GROUP BY DEPART.DEPT_NO, DNOM, LOC;

Construir un disparador que permita realizar operaciones de actualización en la tabla depart a partir de la vista dptos, de forma similar al ejemplo del trigger t_ges_emplead. Se contemplarán las siguientes operaciones:

• Insertar departamento.
• Borrar departamento.
• Modificar la localidad de un departamento.

CREATE OR REPLACE TRIGGER ges_depart
	INSTEAD OF DELETE OR INSERT OR UPDATE
	ON DEPARTAM
	FOR EACH ROW
BEGIN
	IF DELETING THEN
	  	DELETE FROM depart WHERE dept_no = :old.dept_no;
	ELSIF INSERTING THEN
		INSERT INTO depart VALUES(:new.dept_no, :new.dnom, :new.loc);
	ELSIF UPDATING('loc') THEN
		UPDATE depart SET loc = :new.loc WHERE dept_no = :old.dept_no;
	ELSE
	   RAISE_APPLICATION_ERROR (-20001,'Error en la actualizació');
	END IF;
END;
/

</toggledisplay>

Entrega

• entregar els fitxers DAI2AXX_script_NA8.sql i DAI2AXX_NA8.log. Si has treballat per parelles: DAI2AXXXX_script_NA8.sql i DAI2AXXXX_NA8.log, indicant a dins clarament el nom i el número de classe (comentaris amb REM). Exercici individual o per parelles.
• entregar al Moodle: http://192.168.0.15/moodle

Recursos

En el Moodle també pots descarregar el codi per crear les taules, i el codi que s'ha vist a classe.

Durarda

2 hores

---

creat per Joan Quintana Compte, octubre 2009