Exercícios Trigger e Procedure - Respostas

Prévia do material em texto

�PAGE �
�PAGE �4�
Banco de Dados II
George Hamilton
Em cima da tabela de EMPREGADO_JN abaixo, faça um trigger de auditoria que armazene as informações do empregado, bem como, o evento( delete ou update) que disparou este trigger, o usuário responsável pela alteração e a data do sistema.
Abaixo segue a Tabela que irá armazenar as informações necessárias para a manter o histórico:
CREATE TABLE EMPREGADO_JN (
 Operacao 		VARCHAR2(3) NOT NULL,
 Usuario 		VARCHAR2(30) NOT NULL,
 Data 	 	DATE NOT NULL,
 CPF 		CHAR(12) NOT NULL,
 Num_Departamento SMALLINT NOT NULL,
 Nome 	VARCHAR(30) NULL,
 Data_nascimento 	DATE NULL,
 Endereco 	VARCHAR(40) NULL,
 Sexo 	CHAR(1) NULL,
 Salario 	DECIMAL(10,2) NULL,
 Supervisor 	CHAR(12) NULL);
CREATE OR REPLACE TRIGGER AUDIT_EMPREGADO 
AFTER DELETE OR UPDATE ON EMPREGADO 
FOR EACH ROW
DECLARE 
OPERACAO VARCHAR2(3);
BEGIN
IF DELETING
 		THEN OPERACAO:='DEL';
 		ELSE OPERACAO:='UPD';
END IF;
INSERT INTO EMPREGADO_JN VALUES(OPERACAO,USER,SYSDATE, 
:OLD.CPF,:OLD.NUM_DEPARTAMENTO, :OLD.NOME,:OLD.DATA_NASCIMENTO, :OLD.ENDERECO ,:OLD.SEXO ,:OLD.SALARIO ,:OLD.SUPERVISOR);
END;
/
2) Faça um trigger para manter a faixa salarial em valores mínimo e máximo já estabelecidos anteriormente
CREATE OR REPLACE TRIGGER CHECASAL
BEFORE INSERT OR UPDATE OF SALARIO ON EMPREGADO
FOR EACH ROW
DECLARE
SALMIN EMPREGADO.SALARIO%TYPE;
SALMAX EMPREGADO.SALARIO%TYPE;
BEGIN
 SELECT MIN(SALARIO),MAX(SALARIO)
 INTO SALMIN,SALMAX
 FROM EMPREGADO;
 IF :NEW.SALARIO < SALMIN OR :NEW.SALARIO>SALMAX THEN
 RAISE_APPLICATION_ERROR(-20010,'SALARIO FORA DA FAIXA DOS SALARIOS 
MINIMO E MAXIMO PERMITIDOS');
 END IF;
END;
/
3) Faça um trigger para não permitir a diminuição ou o aumento superior a 50% dos salários dos empregados, emitindo uma mensagem de erro.
CREATE OR REPLACE TRIGGER CHSAL
BEFORE UPDATE OF SALARIO ON EMPREGADO
FOR EACH ROW
WHEN ((NEW.SALARIO<OLD.SALARIO) OR (NEW.SALARIO>OLD.SALARIO * 1.5))
BEGIN
 RAISE_APPLICATION_ERROR(-20345,'NAO DIMINUA O SALARIO OU
 AUMENTE MAIS DE 50%');
END;
/
4) Faça um trigger para derivar automaticamente o valor do número de empregados em um determinado departamento.
CREATE OR REPLACE TRIGGER COMPTNUM
AFTER INSERT OR DELETE ON EMPREGADO
FOR EACH ROW
BEGIN
 IF DELETING
 THEN
 UPDATE DEPARTAMENTO
 SET NUMEMP=NUMEMP-1
 WHERE Num_Departamento=:OLD.Num_Departamento;
 END IF;
 IF INSERTING
 THEN
 UPDATE DEPARTAMENTO
 SET NUMEMP=NUMEMP+1
 WHERE Num_Departamento=:NEW.Num_Departamento;
 END IF;
END;
/
5) Faça um trigger para manter o mínimo de 10 horas que um funcionário deverá trabalhar em um projeto.
CREATE OR REPLACE TRIGGER CKHORA
AFTER INSERT OR UPDATE OF HORAS ON TRABALHO
FOR EACH ROW
BEGIN
 IF :NEW.HORAS < 10
 THEN
 UPDATE TRABALHO
 SET HORAS=10
 WHERE CPF_EMPR=:NEW.CPF_EMPR
 AND NUM_PROJETO=:NEW.NUM_PROJETO;
 END IF;
END;
/
6) Faça um trigger para exclusão em cascata de empregado, suas alocações em trabalhos de projetos e de seus dependentes.
CREATE OR REPLACE TRIGGER DELCASEMP
AFTER DELETE ON EMPREGADO
FOR EACH ROW
WHEN (OLD.SUPERVISOR IS NULL)
BEGIN
 
 /* DELECAO CASCATA DE TRABALHO */
 DELETE FROM TRABALHO
 WHERE
 TRABALHO.CPF_EMPR = :OLD.CPF;
 
 /* DELECAO CASCATA DE DEPENDENTE */
 DELETE FROM DEPENDENTE
 WHERE
 DEPENDENTE.CPF_EMPR = :OLD.CPF;
END;
/
7) Faça um trigger de segurança, para restringir o horário de manipulação da tabela de empregados entre às 08:00 e 18:00 horas dos dias úteis.
CREATE OR REPLACE TRIGGER EMPSEG
BEFORE DELETE OR INSERT OR UPDATE ON EMPREGADO
BEGIN
 IF(TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))
 OR (TO_NUMBER(SYSDATE,'HH24') NOT BETWEEN 8 AND 18)
 THEN
 IF DELETING THEN
 RAISE_APPLICATION_ERROR(-20503,'EMPREGADO SO PODE SER EXCLUIDO
 DURANTE HORARIO NORMAL');
 ELSIF INSERTING THEN
 RAISE_APPLICATION_ERROR(-20504,' EMPREGADO SO PODE SER INSERIDO
 DURANTE HORARIO NORMAL');
 ELSIF UPDATING ('SALARIO') THEN
 RAISE_APPLICATION_ERROR(-20505,'SALARIO DO EMPREGADO SO PODE SER 
 ATUALIZADO DURANTE HORARIO NORMAL');
 ELSE 
 /* ATUALIZACOES GERAIS */
 RAISE_APPLICATION_ERROR(-20506,'O EMPREGADO SO PODE SER 
 ATUALIZADO DURANTE HORARIO NORMAL');
 END IF;
 END IF;
END;
/
8) Faça uma procedure para atualizar o salário de um empregado.
CREATE OR REPLACE PROCEDURE ATUALIZA_SAL
(V_CPF IN EMPREGADO.CPF%TYPE,
	 V_NOVO_SALARIO IN EMPREGADO.SALARIO%TYPE)
IS
BEGIN
 UPDATE EMPREGADO
 SET SALARIO = V_NOVO_SALARIO
 WHERE CPF = V_CPF;
 COMMIT;
END ATUALIZA _SAL;
/
9) Faça uma procedure para excluir registros da tabela empregados, informando a quantidade excluída.
CREATE OR REPLACE PROCEDURE EXCLUI_REGISTRO 
 (V_CPF IN EMPREGADO.CPF%TYPE,
 V_REG_EXC OUT VARCHAR2)
IS
BEGIN
 DELETE FROM EMPREGADO
 WHERE CPF = V_CPF;
 V_REG_EXC:= TO_CHAR(SQL%ROWCOUNT)||' registros excluídos.';
END EXCLUI_REGISTRO;
/
Faça uma procedure para atualizar o nome do departamento
CREATE OR REPLACE PROCEDURE ATUALIZA_NOMEDEP
 (V_DEP_ID DEPARTAMENTO.NUM_DEPARTAMENTO%TYPE,
 V_NOME VARCHAR2)
IS
BEGIN
 UPDATE DEPARTAMENTO
 SET NOME =V_NOME
 WHERE NUM_DEPARTAMENTO = V_DEP_ID;
 COMMIT;
END ATUALIZA_NOMEDEP;