• Author / Uploaded
• Jesús José Navarrete Baca

Citation preview

PhD. José Saúl de Lira Miramontes.

Universidad Autónoma de Chihuahua Facultad de Ingeniería

Alumno: PTBI Jesús José Navarrete Baca (301429) Grupo: 7HW1 Actividad: Tarea 8 (Triggers) Fecha: 23/09/2018

~EJERCICIO #1 1. Escribir un Trigger sobre la tabla de Employees que valide al momento de insertar o actualizar el salario de un empleado, que este se encuentre en el rango que le corresponde de acuerdo al puesto que ocupa dicho empleado, de lo contrario generar una excepción. CREATE OR REPLACE TRIGGER TRIGGER_BEFIUSAL_EMPLOYEES BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES FOR EACH ROW BEGIN IF UPDATING THEN FOR Z IN (SELECT MIN_SALARY A, MAX_SALARY B FROM(SELECT * FROM JOBS WHERE JOB_ID = :OLD.JOB_ID)) LOOP IF(:NEW.SALARY < Z.A OR :NEW.SALARY > Z.B) THEN RAISE_APPLICATION_ERROR(-20001,'Salario fuera de los limites establecidos...'); END IF; END LOOP;

ELSIF INSERTING THEN FOR Z IN (SELECT MIN_SALARY A, MAX_SALARY B FROM(SELECT * FROM JOBS WHERE JOB_ID = :NEW.JOB_ID)) LOOP IF(:NEW.SALARY < Z.A OR :NEW.SALARY > Z.B) THEN RAISE_APPLICATION_ERROR(-20001,'Salario fuera de los limites establecidos...'); END IF; END LOOP; END IF;

END;

DECLARE SALEXCEPT EXCEPTION ; PRAGMA EXCEPTION_INIT(SALEXCEPT, -20001);

0

PROCEDURE EPRINT(TXT VARCHAR2)IS BEGIN DBMS_OUTPUT.PUT_LINE(TXT); END; BEGIN BEGIN INSERT INTO EMPLOYEES VALUES(500,'JESUS','NAVARRETE','[email protected]','123456789','13-MAR2011','AD_VP',1,NULL,NULL,NULL); EPRINT('>>Se ha insertado empleado...'); EXCEPTION WHEN SALEXCEPT THEN EPRINT('-Salario Invalido... *Consulte margenes de salario para puesto.'); WHEN OTHERS THEN EPRINT('*Se ha encontrado una excepcion al insertar empleado: '); EPRINT(SQLERRM); END; BEGIN UPDATE EMPLOYEES SET SALARY = 100 WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE ROWNUM = 1); EPRINT('>>Se ha actualizado empleado...');

EXCEPTION WHEN SALEXCEPT THEN EPRINT('-Salario Invalido... *Consulte margenes de salario para puesto.'); WHEN OTHERS THEN EPRINT('*Se ha encontrado una excepcion al actualizar empleado: '); EPRINT(SQLERRM); END; END;

1

2

~EJERCICIO #2 Escribir un Trigger que replique las actualizaciones de la tabla de Job_History en una tabla con nombre RJob_History. CREATE OR REPLACE PROCEDURE ADD_RJOB_HISTORY(EID JOB_HISTORY.EMPLOYEE_ID%TYPE, SD JOB_HISTORY.START_DATE%TYPE, ED JOB_HISTORY.END_DATE%TYPE, JID JOB_HISTORY.JOB_ID%TYPE, DID JOB_HISTORY.DEPARTMENT_ID%TYPE) AS BEGIN INSERT INTO RJOB_HISTORY VALUES(EID,SD,ED,JID,DID); END;

CREATE OR REPLACE TRIGGER TRIGGER_ABCJHISTORY_RJH AFTER UPDATE OR INSERT OR DELETE ON JOB_HISTORY FOR EACH ROW BEGIN

IF UPDATING THEN ADD_RJOB_HISTORY(:NEW.EMPLOYEE_ID, :NEW.START_DATE, :NEW.END_DATE, :NEW.JOB_ID, :NEW.DEPARTMENT_ID); ELSIF INSERTING THEN ADD_RJOB_HISTORY(:NEW.EMPLOYEE_ID, :NEW.START_DATE, :NEW.END_DATE, :NEW.JOB_ID, :NEW.DEPARTMENT_ID); ELSE ADD_RJOB_HISTORY(:OLD.EMPLOYEE_ID, :OLD.START_DATE, :OLD.END_DATE, :OLD.JOB_ID, :OLD.DEPARTMENT_ID); END IF; END;

DECLARE BEGIN BEGIN EXECUTE IMMEDIATE 'CREATE TABLE RJob_History AS SELECT * FROM JOB_HISTORY WHERE 1 1'; EXCEPTION WHEN OTHERS THEN NULL; END;

3

DELETE FROM RJOB_HISTORY; --CASO DEMOSTRATIVO

UPDATE JOB_HISTORY SET END_DATE = SYSDATE WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE ROWNUM =1);

DELETE FROM JOB_HISTORY WHERE ROWNUM = 1;

INSERT INTO JOB_HISTORY SELECT * FROM RJOB_HISTORY WHERE ROWID = (SELECT MAX (ROWID) FROM RJOB_HISTORY);

FOR X IN (SELECT * FROM RJOB_HISTORY) LOOP DBMS_OUTPUT.PUT_LINE(X.EMPLOYEE_ID||' '||X.DEPARTMENT_ID||' '||X.JOB_ID||' '||X.START_DATE||' '||X.END_DATE); END LOOP; END;

4

~EJERCICIO #3 Escribir un Trigger que almacene en una tabla Bitácora, el usuario, fecha, hora y el tipo de operación realizada sobre la tabla de Jobs ( I= Insert, U= Update, D= Delete). DROP TABLE BITACORA_JOBS; CREATE TABLE BITACORA_JOBS ( BJ_ID INTEGER PRIMARY KEY, USR VARCHAR2(50) NOT NULL, BJ_DATE DATE NOT NULL, BJ_TIME VARCHAR2(10) NOT NULL, BJ_TYPE VARCHAR2(50) NOT NULL );

CREATE OR REPLACE FUNCTION GETTIME RETURN VARCHAR2 AS SEND VARCHAR2(50); BEGIN SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') INTO SEND FROM DUAL; RETURN SEND; END;

DROP SEQUENCE SEQ_BITJOBS; CREATE SEQUENCE SEQ_BITJOBS MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE OR REPLACE PROCEDURE ADD_BITACORAJOBS(OPN VARCHAR2) AS BEGIN INSERT INTO BITACORA_JOBS VALUES(SEQ_BITJOBS.NEXTVAL, SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL),

(SELECT

SYSDATE, GETTIME, OPN); END;

CREATE OR REPLACE TRIGGER TRIGGER_BITACORAJOBS AFTER INSERT OR UPDATE OR DELETE ON JOBS

5

FOR EACH ROW BEGIN CASE WHEN INSERTING THEN ADD_BITACORAJOBS('I'); WHEN UPDATING THEN ADD_BITACORAJOBS('U'); WHEN DELETING THEN ADD_BITACORAJOBS('D'); END CASE; END;

DECLARE BEGIN

UPDATE JOBS SET MAX_SALARY = (SELECT MAX_SALARY + (MAX_SALARY /100) FROM JOBS WHERE ROWNUM = 1) WHERE ROWNUM =1; INSERT INTO JOBS VALUES('500', 'TEST',500, 1000); DELETE FROM JOBS WHERE JOB_ID='500'; UPDATE JOBS SET MAX_SALARY = (SELECT MAX_SALARY - (MAX_SALARY /100) FROM JOBS WHERE ROWNUM = 1) WHERE ROWNUM =1;

FOR Z IN (SELECT * FROM BITACORA_JOBS) LOOP DBMS_OUTPUT.PUT_LINE(Z.BJ_ID||' '||Z.BJ_TIME||' '||Z.BJ_TYPE); END LOOP; END;

6

'||Z.USR||'

'||Z.BJ_DATE||'

7

~EJERCICIO #4 Escribir un Trigger sobre la tabla de Employees que valide para las operaciones insert y update lo siguiente(deshabilitar el disparador del punto 1): a. Que exista el puesto b. El sueldo se encuentre en el rango del puesto que ocupa c. Que exista el departamento d. Que el jefe asignado exista ALTER TRIGGER TRIGGER_BEFIUSAL_EMPLOYEES DISABLE;

CREATE OR REPLACE FUNCTION EXISTPUESTO(JID JOBS.JOB_ID%TYPE) RETURN BOOLEAN AS FLAG BOOLEAN:=FALSE; BEGIN

BEGIN FOR Z IN (SELECT * FROM JOBS WHERE JOB_ID = JID) LOOP FLAG := NOT FLAG; END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END;

RETURN FLAG; END;

CREATE OR REPLACE FUNCTION SUELDOISONRANGE(MONT EMPLOYEES.SALARY%TYPE, JID JOBS.JOB_ID%TYPE) RETURN BOOLEAN AS FLAG BOOLEAN := FALSE; BEGIN BEGIN FOR Z IN (SELECT MIN_SALARY A, MAX_SALARY B FROM JOBS WHERE JOB_ID = JID)LOOP IF(MONT >= Z.A AND MONT