M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T15

De wikiserver
Dreceres ràpides: navegació, cerca

T15- Creació de triggers

  • Exercici 1

Les operacions DML sobre taules només es permetran en hores de feina, es a dir, entre les 8:45 del matí i las 5:50 de la tarda; de dilluns a divendres.

a) crea un procediment emmagatzemat anomenat SECURE_DML que mostri un missatge d'error, en cas de no complir la regla anterior, com "Només es pot modificar dades en horari de feina".

CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:45' AND '17:30'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'Només es permeten canvis en horari doficina.');
END IF;
END secure_dml;
/
  • Exercici 2

Crea un trigger en la taula PRODUCT que cridi al procediment anterior.

CREATE OR REPLACE TRIGGER secure_prod
BEFORE INSERT OR UPDATE OR DELETE ON product
BEGIN
secure_dml();
END secure_prod;

a) prova el procediment modificant la franja horària proposada anteriorment e intentant afegir un nou registre en la taula PRODUCT. Després de la comprovació restableix els valors originals. 

SQL> INSERT INTO product
  2  VALUES (99999, 'El meu producte');
  • Exercici 3

La comissió d'un venedor canviaria amb qualsevol comanda nova o canvis en les comandes existents. La seva comissió s'emmagatzema en la columna COMM de la taula EMP. En la taula CUSTOMER s'assigna un venedor a un client particular.

a) crea un procediment que actualitzarà la comissió del venedor. Utilitzaràs paràmetres per enviar l'identificador del client, el total antic de la comanda i el total nou de la comanda, des del trigger que es fa la crida. El procediment necessitarà localitzar el codi del empleat en la taula CUSTOMER i actualitzar el registre del venedor en la taula EMP, afegint una nova comissió al valor existent (percentatge de comissió el 5% del total de la comanda).

CREATE OR REPLACE PROCEDURE update_comm
(v_custid IN NUMBER,
v_old_tot IN NUMBER,
v_new_tot IN NUMBER)
IS
v_repid NUMBER;
v_comm NUMBER;
BEGIN
SELECT repid
INTO v_repid
FROM customer
WHERE custid = v_custid;
v_comm := (NVL(v_new_tot, 0) - NVL(v_old_tot, 0)) * .05;
UPDATE emp
SET comm = comm + v_comm
WHERE empno = v_repid;
END update_comm;
/

b) Crea un trigger en la taula ORD que cridi al procediment, passant els paràmetres necessaris.

CREATE OR REPLACE TRIGGER update_emp_comm
AFTER INSERT OR UPDATE OR DELETE ON ord
FOR EACH ROW
BEGIN
update_comm (:new.custid, :old.total, :new.total);
END;
/

c) Modifica la comanda 601 per assignar-li un total de 3$. Comprova que la comissió de WARD s'ha incrementat en 0.03. La comissió original era de 500. 

SQL> UPDATE ord
  2  SET total = 3
  3  WHERE ordid = 601;

SQL> SELECT ename, comm
  2  FROM emp
  3  WHERE ename = 'WARD';

  ENAME         COMM
  -------  ---------
  WARD        500.03


  • Exercici 4

A las taules EMP i DEPT se'ls aplica una sèrie de regles de negoci.

Implementa les regles de negoci mitjançant triggers.

Reglas de negocio:

1. La taula EMP hauria de contenir exactament un PRESIDENT. Comproveu la resposta.

CREATE OR REPLACE TRIGGER presidente
BEFORE DELETE OR INSERT OR UPDATE OF JOB ON emp
FOR EACH ROW
BEGIN
IF DELETING AND :OLD.JOB = 'PRESIDENT' THEN
RAISE_APPLICATION_ERROR (-20001, 'No es pot eliminar al PRESIDENT');
END IF;
IF UPDATING THEN
IF :OLD.JOB = 'PRESIDENT' THEN
IF :NEW.JOB != 'PRESIDENT' THEN
RAISE_APPLICATION_ERROR (-20002, 'EL PRESIDENT no pot deixar de ser-ho');
END IF;
ELSE
IF :NEW.JOB = 'PRESIDENT' THEN
RAISE_APPLICATION_ERROR (-20003, 'Ja tenim un PRESIDENT');
END IF;
END IF;
END IF;
IF INSERTING AND :NEW.JOB = 'PRESIDENT' THEN
RAISE_APPLICATION_ERROR (-20003, 'Ja tenim un PRESIDENT');
END IF;
END presidente;
/
SQL> INSERT INTO emp (empno, ename, job, deptno)
  2  VALUES (7800,'HARRIS','PRESIDENT',20);

2. Els salaris només podran augmentar-se, no disminuir-se.

CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal < old.sal)
BEGIN
RAISE_APPLICATION_ERROR (-20004, 'Els salaris no es poden reduir');
END check_sal;
/
SQL> UPDATE emp
  2  SET sal = 1100
  3  WHERE empno = 7934;

3. Si un departament es trasllada a una altra ubicació, cada empleat d'aquest departament tindrà automàticament un increment de salari del 2%.

CREATE OR REPLACE TRIGGER change_location
AFTER UPDATE OF loc ON dept
FOR EACH ROW
WHEN (OLD.loc != NEW.loc)
BEGIN
UPDATE emp
SET sal = sal * 1.02
WHERE deptno = :NEW.deptno;
END change_location;
/
SQL> SELECT ename, sal, deptno
  2  FROM emp
  3  WHERE deptno = 30;

  ENAME          SAL       DETNO
  -------  ---------  ----------
  BLAKE         2850          30
  MARTIN        1250          30
  ALLEN         1600          30
     ...   

SQL> UPDATE dept
  2  SET loc = 'HOUSTON'
  3  WHERE deptno = 30;

SQL> SELECT ename, sal, deptno
  2  FROM emp
  3  WHERE deptno = 30;

  ENAME          SAL       DETNO
  -------  ---------  ----------
  BLAKE         2907          30
  MARTIN        1275          30
  ALLEN         1632          30
     ...
Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF3:_Llenguatges_SQL_/_Solucions_PL-SQL_T15&oldid=4879»