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;
