ABD - Ficha 6

/*1*/
ALTER TABLE EMP ADD(divisa VARCHAR2(3) DEFAULT 'PTE');
ALTER TABLE EMP ADD CHECK(divisa IN ('EUR','PTE'));

CREATE OR REPLACE TRIGGER divch
  Before UPDATE OF Divisa ON Emp
  FOR Each ROW

BEGIN
  IF :NEW.divisa = 'EUR' THEN
    :NEW.sal := :NEW.sal / 200.482;
  ELSE
    :NEW.sal := :NEW.sal * 200.482;
  END IF;
END;

UPDATE Emp SET divisa = 'EUR' WHERE empno = 1693;
/*2*/
CREATE OR REPLACE TRIGGER deptch
  Before UPDATE OF deptno ON Emp
  FOR Each ROW
  WHEN (NEW.deptno <> old.deptno)

BEGIN
  raise_application_error(-20600,'Não é permitod mudar de departamento');
END;

UPDATE Emp SET deptno = 20 WHERE empno = 1693;
DROP TRIGGER salch;
/*3*/
CREATE OR REPLACE TRIGGER salch
  Before UPDATE OF sal ON Emp
  FOR Each ROW

BEGIN
  IF (:NEW.sal <= :old.sal) THEN
    raise_application_error(-20600,'Não é permitod baixar o salario');
  END IF;
END;

UPDATE Emp SET sal = 100 WHERE empno = 1693;
UPDATE Emp SET sal = 10 WHERE empno = 1693;

/*4*/
CREATE OR REPLACE TRIGGER salch
  Before UPDATE OR INSERT OR DELETE ON Emp
  FOR Each ROW

DECLARE hora NUMBER(2);

BEGIN
  SELECT (TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))) INTO hora FROM dual;
  IF hora < 8 OR hora > 19  THEN
    raise_application_error(-20600,'Fora de Horas!!');
  END IF;
END;