Joan a 18:50, 12 gen 2011

2011-01-12T18:50:45Z

Pàgina nova

==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
<pre>
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;
</pre>
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

<pre>
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;
/
</pre>

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)

<pre>
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;
/
</pre>

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%.

<pre>
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 ...
*/
</pre>

4. Suponiendo que disponemos de la vista

<pre>
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;
</pre>

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.

<pre>
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;
/
</pre>
</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

{{Autor}}, octubre 2009
[[Categoria: IES Jaume Balmes]]
[[Categoria: Pràctiques DAI-C8-EC]]

DAI-C8-EC: Triggers, exercicis proposats - Historial de revisió

Joan a 18:50, 12 gen 2011