ListaExerc#U00edcios TRIGGER

Prévia do material em texto

Lista de Exercícios de Banco de Dados 2 - TRIGGER
Dr. Sylvio Barbon Junior
Estrutura
EX2_CLIENTE: codcliente, nome, datanascimento, cpf;
EX2_PEDIDO: codpedido, codcliente, datapedido, nf, valortotal;
EX2_ITEMPEDIDO: codpedido, numeroitem, valorunitario, quantidade, codproduto;
EX2_PRODUTO: codproduto, descricao, quantidade;
EX2_LOG: codlog, data, descricao;
EX2_REQUISICAO_COMPRA: codrequisicaocompra, codproduto, data, quantidade; 
SQL
CREATE TABLE EX2_CLIENTE(
 codcliente int,
 nome varchar(60),
 datanascimento date,
 cpf varchar(11),
 CONSTRAINT pk2_EX2_cliente PRIMARY KEY (codcliente)
);
CREATE TABLE EX2_PEDIDO(
 codpedido int,
 codcliente int,
 datapedido date,
 nf varchar(12),
 valortotal decimal(10,2),
 CONSTRAINT pk2_EX2_pedido PRIMARY KEY (codpedido),
 CONSTRAINT pk2_EX2_pedido_cliente FOREIGN KEY (codcliente) REFERENCES EX2_CLIENTE(codcliente)
);
CREATE TABLE EX2_PRODUTO(
 codproduto int,
 descricao varchar(100),
 quantidade int,
 CONSTRAINT pk2_EX2_produto PRIMARY KEY (codproduto)
);
CREATE TABLE EX2_ITEMPEDIDO(
 codpedido int,
 numeroitem int,
 valorunitario decimal(10,2),
 quantidade int,
 codproduto int,
 CONSTRAINT pk2_itempedido PRIMARY KEY (codpedido, numeroitem),
 CONSTRAINT fk2_codpedido FOREIGN KEY (codpedido) REFERENCES EX2_PEDIDO (codpedido),
 CONSTRAINT fk2_itempedido_produto FOREIGN KEY (codproduto) REFERENCES EX2_PRODUTO 
(codproduto)
);
CREATE TABLE EX2_LOG(
 codlog int,
 data date,
 descricao varchar(255),
 CONSTRAINT pk2_ex2_log PRIMARY KEY (codlog)
);
CREATE TABLE EX2_REQUISICAO_COMPRA(
 codrequisicaocompra int,
 codproduto int,
 data date,
 quantidade int,
 CONSTRAINT pk2_ex2_reqcompra PRIMARY KEY (codrequisicaocompra),
 CONSTRAINT fk2_ex2_reqcompra_produto FOREIGN KEY (codproduto) REFERENCES 
EX2_PRODUTO(codproduto)
);
 
INSERT INTO EX2_CLIENTE VALUES (1, 'Sylvio Barbon', TO_DATE('05/12/1984', 'DD/MM/YYYY'), 
'12315541212');
INSERT INTO EX2_CLIENTE VALUES (2, 'Antonio Carlos da Silva', TO_DATE('01/11/1970', 'DD/MM/YYYY'), 
'12313345512');
INSERT INTO EX2_CLIENTE VALUES (3, 'Thiago Ribeiro', TO_DATE('15/11/1964', 'DD/MM/YYYY'), 
'12315544411');
INSERT INTO EX2_CLIENTE VALUES (4, 'Carlos Eduardo', TO_DATE('25/10/1924', 'DD/MM/YYYY'), 
'42515541212');
INSERT INTO EX2_CLIENTE VALUES (5, 'Maria Cristina Goes', TO_DATE('03/11/1981', 'DD/MM/YYYY'), 
'67715541212');
INSERT INTO EX2_CLIENTE VALUES (6, 'Ruan Manoel Fanjo', TO_DATE('06/12/1983', 'DD/MM/YYYY'), 
'32415541212');
INSERT INTO EX2_CLIENTE VALUES (7, 'Patrícia Marques', TO_DATE('01/02/1944', 'DD/MM/YYYY'), 
'77715541212');
INSERT INTO EX2_PRODUTO VALUES (1, 'Mouse', 10);
INSERT INTO EX2_PRODUTO VALUES (2, 'Teclado', 10);
INSERT INTO EX2_PRODUTO VALUES (3, 'Monitor LCD', 10);
INSERT INTO EX2_PRODUTO VALUES (4, 'Caixas Acústicas', 10);
INSERT INTO EX2_PRODUTO VALUES (5, 'Scanner de Mesa', 10);
INSERT INTO EX2_PEDIDO VALUES (1, 1, TO_DATE('01/04/2012', 'DD/MM/YYYY'), '00001', 400.00);
INSERT INTO EX2_ITEMPEDIDO VALUES (1, 1, 10.90, 1, 1);
INSERT INTO EX2_ITEMPEDIDO VALUES (1, 2, 389.10, 1, 3);
INSERT INTO EX2_PEDIDO VALUES (2, 2, TO_DATE('01/04/2012', 'DD/MM/YYYY'), '00002', 10.90);
INSERT INTO EX2_ITEMPEDIDO VALUES (2, 1, 10.90, 1, 1);
INSERT INTO EX2_PEDIDO VALUES (3, 2, TO_DATE('01/04/2012', 'DD/MM/YYYY'), '00003', 21.80);
INSERT INTO EX2_ITEMPEDIDO VALUES (3, 1, 10.90, 1, 1);
INSERT INTO EX2_PEDIDO VALUES (4, 3, TO_DATE('01/05/2012', 'DD/MM/YYYY'), '00004', 169.10);
INSERT INTO EX2_ITEMPEDIDO VALUES (4, 1, 10.90, 1, 1);
INSERT INTO EX2_ITEMPEDIDO VALUES (4, 2, 15.90, 2, 2);
INSERT INTO EX2_ITEMPEDIDO VALUES (4, 3, 25.50, 1, 4);
INSERT INTO EX2_ITEMPEDIDO VALUES (4, 4, 100.90, 1, 5);
INSERT INTO EX2_PEDIDO VALUES (5, 4, TO_DATE('01/05/2012', 'DD/MM/YYYY'), '00005', 100.90);
INSERT INTO EX2_ITEMPEDIDO VALUES (5, 1, 100.90, 1, 5);
INSERT INTO EX2_PEDIDO VALUES (6, 6, TO_DATE('02/05/2012', 'DD/MM/YYYY'), '00006', 51.35);
INSERT INTO EX2_ITEMPEDIDO VALUES (6, 1, 25.50, 2, 4);
CREATE SEQUENCE EX2_LOG_SEQ
 START WITH 1
 INCREMENT BY 1
 MAXVALUE 99999
 MINVALUE 1
 CACHE 20
 ;
EXERCÍCIOS TRIGGER
1) Crie um TRIGGER para baixar o estoque de um PRODUTO quando ele for vendido;
--Criando o Trigger
CREATE OR REPLACE TRIGGER ex2_baixar_estoque_trigger
AFTER INSERT ON EX2_ITEMPEDIDO
FOR EACH ROW
BEGIN
 
 UPDATE EX2_PRODUTO p SET p.quantidade = p.quantidade - :NEW.quantidade
 WHERE p.codproduto = :NEW.codproduto;
 
END;
--Verificando o Estoque
SELECT * FROM EX2_PRODUTO;
//Criando uma Inserção de Pedido e seus Itens
INSERT INTO EX2_PEDIDO VALUES (7, 1, TO_DATE('01/04/2012', 'DD/MM/YYYY'), '00001', 400.00);
INSERT INTO EX2_ITEMPEDIDO VALUES (7, 1, 10.90, 1, 1);
INSERT INTO EX2_ITEMPEDIDO VALUES (7, 2, 389.10, 1, 3);
--Verificando o Estoque
SELECT * FROM EX2_PRODUTO;
2) Crie um TRIGGER para criar um log dos CLIENTES modificados;
--CRIANDO TRIGGER
CREATE OR REPLACE TRIGGER ex2_log_clientes
AFTER INSERT ON EX2_CLIENTE
FOR EACH ROW
BEGIN
 
 INSERT INTO EX2_LOG 
 VALUES (EX2_LOG_SEQ.nextval, TO_DATE(SYSDATE), 'INSERINDO NOVO CLIENTE - '||:NEW.CPF);
 
END;
--CRIANDO UM NOVO CLIENTE
INSERT INTO EX2_CLIENTE 
VALUES (8, 'José Adolfo', TO_DATE('05/11/1998', 'DD/MM/YYYY'), '55555555591');
--VERIFICANDO TABELA DE LOG
SELECT * FROM EX2_LOG
3) Crie um TRIGGER para criar um log dos PRODUTOS atualizados;
4) Crie um TRIGGER para criar um log quando não existir a quantidade do ITEMPEDIDO em estoque;
5) Crie um TRIGGER para criar uma requisição de REQUISICAO_COMPRA quando o estoque atingir 
50% da venda mensal;
6) Crie um TRIGGER para criar um log quando um ITEMPEDIDO for removido;
7) Crie um TRIGGER para criar um LOG quando o valor total do pedido for maior que R$1000.
CREATE OR REPLACE TRIGGER ex2_log_pedidos_caros
AFTER INSERT ON EX2_PEDIDO
FOR EACH ROW
WHEN (NEW.valortotal > 1000)
BEGIN
 
 INSERT INTO EX2_LOG 
 VALUES (EX2_LOG_SEQ.nextval, SYSDATE, 'PEDIDO CARO - '||:NEW.codpedido);
 
 --mensagem na tela
 dbms_output.put_line('Pedido Caro registrado - '||:NEW.codpedido);
 
END;
8) Crie um TRIGGER para NÃO deixar valores negativos serem INSERIDOS em ITEMPEDIDO, o valor 
mínimo é "0";
9) Crie um TRIGGER que NÃO permita que uma PESSOA com data de nascimento anterior a data de 
hoje seja inserida ou atualizada.
-- resposta alternativa 1 – mais eficiente
CREATE OR REPLACE TRIGGER EX2_CLIENTE_DATA
BEFORE INSERT OR UPDATE ON EX2_CLIENTE
FOR EACH ROW
WHEN (NEW.DATANASCIMENTO > SYSDATE)
BEGIN
 raise_application_error(-20001,'Data inválida, maior do que data atual');
END;
-- resposta alternativa2
CREATE OR REPLACE TRIGGER EX2_CLIENTE_DATA
BEFORE INSERT OR UPDATE ON EX2_CLIENTE
FOR EACH ROW
BEGIN
 IF (:NEW.DATANASCIMENTO > SYSDATE) THEN
 raise_application_error(-20001,'Data inválida, maior do que data atual');
 END IF;
END;
10) Crie um TRIGGER para automaticamente numerar o ITEMPEDIDO.numeroitem de acordo com 
valores anteriores do mesmo PEDIDO.
11) Crie um TRIGGER para não permitir quantidade negativa no ITEMPEDIDO.
12) Crie um TRIGGER para acrescentar a palavra "Sr(a)" ao nome das PESSOAS que tem nasceram há 
mais de 30 anos.
13) Crie um TRIGGER para retornar a quantidade em estoque de um ITEMPEDIDO que foi removido.
14) Crie um TRIGGER para remover as REQUISICOESCOMPRA de um produto que é removido.
15) Crie um TRIGGER que ajuste os pedidos de compra para que não existam itens repetidos, ou seja, 
quando o mesmo ITEMPEDIDO for inserido deve-se disparar uma mensagem.
CREATE OR REPLACE TRIGGER EX2_ITEMPEDIDO_VALIDAR
BEFORE INSERT ON EX2_ITEMPEDIDO
FOR EACH ROW
DECLARE
 var_contador_item int;
BEGIN
 SELECT COUNT(CODPRODUTO) INTO var_contador_item
 FROM EX2_ITEMPEDIDO
 WHERE EX2_ITEMPEDIDO.CODPRODUTO = :NEW.CODPRODUTO
 AND EX2_ITEMPEDIDO.CODPEDIDO = :NEW.CODPEDIDO;
 IF (var_contador_item>= 1) THEN
 raise_application_error(-20000,'ITEM REPETIDO, AJUSTAR QUANTIDADE DE ITEM EXISTENTE!');
 END IF;
 
END;