002 - Triggers Exercicios

TRIGGER
A trigger, que na tradução literal significa gatilho, são execuções armazenadas que disparam conforme algum
evento específico e/ou pré-definido no banco de dados. Os triggers são executados conforme alguns dos eventos
abaixo:
 Comando DML- Data Manipulation Language (Delete, Insert, Update)

 Comandos DDL – Data Definition Language (Create, Alter, Drop)
 Operações no banco de dados (Logon, Logoff, Startup, Shutdown)
As triggers geralmente são criadas para logs de auditoria, controle de dados sensíveis, replicações de dados e
outras finalidades. A utilização desse objeto requer cuidado e muito controle, para não invalidar operações ou
onerar o ambiente de banco de dados.
Segue abaixo, a estrutura de criação de uma trigger:
CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Estrutura:
 CREATE [OR REPLACE] TRIGGER trigger_name: Criação ou replace de uma trigger existente.
 {BEFORE | AFTER | INSTEAD OF}: Especificação do momento do evento de execução da trigger.
 {INSERT [OR] | UPDATE [OR] | DELETE}: Operações DML a serem executadas.

Material de Apoio – SQL 1.0
 [OF col_name]: Especificação de uma coluna para update
 [ON table_name]: Associação de uma tabela com a trigger.
 [REFERENCING OLD AS o NEW AS n]: Permiti referenciar novos e antigos valores, com INSERT,
UPDATE e DELETE.
 [FOR EACH ROW]: Trigger para criação da trigger a nível de linha, caso contrário, será após a execução
de um comando SQL.
 WHEN (condition): Condição para FOR EACH ROW disparar. Essa cláusula só é válida para triggers a
nível de linhas.

Exemplo
Para exemplificarmos, primeiramente criaremos uma tabela de produtos e uma de auditoria:
create table produto_uni (nome varchar2(30), descricao varchar2(30));
create table produto_aud(dt_audit date, nome varchar2(30), descricao varchar2(30));
Após a criação das tabelas, vamos criar a trigger responsável por guardar o histórico de registros inseridos:
create or replace trigger trg_prod_uni

before delete on produto_uni --
for each row
declare
-- local variables here
begin
INSERT INTO produto_aud
(dt_audit, nome, descricao)
VALUES
(sysdate,:NEW.nome,:NEW.descricao);
END;
E agora vamos inserir valores nessa tabela para depois verificarmos na tabela de auditoria:
insert into produto_uni values('som', 'aparelho de sow de 300w');
insert into produto_uni values('cd', 'pacote de cd');
insert into produto_uni values('monitor', 'monitor de 15 polegadas');

Os valores inseridos deverão estar na tabela de auditoria:
select * from produto_aud
:NEW e :OLD
Nas triggers de linha, é possível acessar os valores que estão sendo executados no momento ou os valores que
já estavam armazenados.
:NEW Valores que estão sendo armazenados no momento, ou seja, são os novos valores que estão sendo
manipulados de acordo com o evento da trigger. São sempre acompanhados da coluna do objeto alvo do evento
da trigger.
:OLD Armazena os valores anteriores que são os alvos da alteração no momento da execução da trigger.
Utilizado para eventos de UPDATE, assim identificando e armazenando os campos alterados.
Exceções
O raise_application_error é uma procedure da Oracle que permite o desenvolvedor enviar ou manipular erros
identificados pela aplicação. Essas exceções permitem tratar erros e documenta-los; todos os erros são
definidos entre -20,000 e -20,999.
create table funcionários (nome varchar2(30) primary key, sobrenome varchar2(30), salario
number(30));
------------------------------------------------------------------------------------
create or replace trigger sysadm.trg_func
before update or delete or insert on sysadm.funcionarios
FOR EACH ROW
declare
begin
if :NEW.salario <> :OLD.salario
and :NEW.salario > :OLD.salario *1.10
then
RAISE_APPLICATION_ERROR(-20512, 'Não podem ser alterados dados dessa tabela');
end if;
if deleting then
RAISE_APPLICATION_ERROR(-20512, 'Não podem ser excluídos dados dessa tabela');

end if;
END;
Exercícios
1) Criar uma trigger (trg_cad_clientes) para auditar todos os inserts realizados em uma tabela de cadastro
de clientes. Organizar a tabela de logs para armazenar todos os campos da tabela com a data do
respectivo insert; e devem ser inseridos no mínimo 5 cadastros para testar o log de auditoria.
create table cadastro_cliente (nm_cliente varchar2(30), sobrenome varchar2(30), dt_cadastro date);
create table cadastro_aud(dt_audit date, nm_cliente varchar2(30), sobrenome varchar2(30),

dt_cadastro date);
create or replace trigger trg_cad_clientes

before insert on cadastro_cliente
for each row
declare
begin
INSERT INTO cadastro_aud
(dt_audit, nm_cliente, sobrenome, dt_cadastro)
VALUES
(sysdate,:NEW.nm_cliente,:NEW.sobrenome, :NEW.dt_cadastro );
END;
insert into cadastro_cliente values('Marcelo','Diniz Augusto', sysdate);
select * from cadastro_aud;
2) Com a mesma tabela do exercício anterior (cadastro_cliente), criar um trigger para armazenar os valores
antes de serem deletados da tabela. Devem ser inseridos 5 pessoas com diferentes nomes e o delete
deve ser feito caso a caso com o respectivo nome.
create or replace trigger trg_cad_clientes

before delete on cadastro_cliente
for each row
declare
begin
INSERT INTO cadastro_aud
(dt_audit, nm_cliente, sobrenome, dt_cadastro)
VALUES
(sysdate,:OLD.nm_cliente,:OLD.sobrenome, :OLD.dt_cadastro );
END;
insert into cadastro_cliente values('Marcelo','Diniz Augusto', sysdate);
delete cadastro_cliente
where nm_cliente='Marcelo'
3) O departamento de Recursos Humanos deseja que os aumentos de salários maiores de R$ 1500,00 sejam
armazenados em uma tabela de auditoria para posterior averiguação. Criar uma tabela de funcionário e
uma trigger (trg_func) que audite somente esses aumentos com o novo valor do salário e o anterior.
create table salario (cd_func number(10), nm_func varchar2(30), salario number(30));
create table salario_aud(dt_audit date, cd_func number(10), nm_func varchar2(30), salario

number(30));
insert into salario values(1,'Marcelo Diniz', 1000);

insert into salario values(2,'Mario Quinello',1000);
insert into salario values(3,'Carlos Roberto', 3000);
insert into salario values(4,'Jose Silva', 5000);
insert into salario values(5,'Marina Bezerra', 10000);
create or replace trigger trg_func

before update on salario
for each row
declare
begin
if :NEW.salario >=:OLD.salario+1500 then
INSERT INTO salario_aud
VALUES
(sysdate,:OLD.cd_func,:OLD.nm_func, :OLD.salario, :NEW.salario );
end if;
END;
4) Criar um trigger para auditar a troca de departamento dos funcionários de uma determinada empresa,
constando o novo e antigo departamento. Para criação dessa trigger, somente será dada a tabela de
departamento e o novo e antigo departamento devem ser auditados. As estruturas como tabela de log e
outras que julgarem necessárias, deverão ser criadas de acordo com a necessidade.
create table dept_emp (cd_funcionario number(10), cd_filial number(10), nm_funcionario

varchar2(30), nm_departamento varchar2(30));
insert into dept_emp values (1, 500, 'Marcelo Diniz', 'TI');

insert into dept_emp values (1, 500, 'Carlos Roberto', 'Finanças');
insert into dept_emp values (1, 500, 'Roberto Almeida', 'RH');
insert into dept_emp values (1, 500, 'Marisa Inacio', 'VENDAS');
create or replace trigger trg_prod_uni

before update on dept_emp
for each row
declare
begin
INSERT INTO dept_emp_log
VALUES
(sysdate,:NEW.cd_funcionario,:NEW.cd_filial, :NEW.nm_funcionario,
:NEW.novo_departamento, OLD.antigo_departamento);
END;
5) Aproveitando a estrutura de criação de tabela do exercício 3, criar uma trigger para gerar um erro no momento
que um aumento de salário for maior que 10%
RAISE_APPLICATION_ERROR(-20512,'O salário não pode ser aumentado em mais de 10%');
create or replace trigger trg_func

before update or delete or drop on salario
for each row
declare
begin
if :NEW.salario >= (:OLD.salario * 0.10) + OLD.salario then
RAISE_APPLICATION_ERROR(-20512,'O salário não pode ser aumentado em mais de 10%');
end if;
END;
6) Criar uma trigger para bloquear qualquer alteração na tabela dept_emp (exercício 4) e gerar um erro de
permissão negada.
create or replace trigger trg_dept

before update or delete or drop on dept_emp
for each row
declare
begin
if :NEW.cd_funcionario<>OLD.cd_funcionario then
RAISE_APPLICATION_ERROR(-20512,'Permissão Negada');
elsif
:NEW.cd_filial<>OLD.cd_filial then
elsif
:NEW.nm_funcionario<>OLD.nm_funcionario then
elsif
:NEW.nm_departamento<>OLD.nm_departamento then
end if;
END;

002 - Triggers Exercicios

Enviado por

Direitos autorais:

Formatos disponíveis

002 - Triggers Exercicios

Enviado por

Dados do documento

Descrição original:

Direitos autorais

Formatos disponíveis

Compartilhar este documento

Compartilhar ou incorporar documento

Opções de compartilhamento

Você considera este documento útil?

Este conteúdo é inapropriado?

Direitos autorais:

Formatos disponíveis

002 - Triggers Exercicios

Enviado por

Direitos autorais:

Formatos disponíveis

TRIGGER

 Comando DML- Data Manipulation Language (Delete, Insert, Update)

Segue abaixo, a estrutura de criação de uma trigger:

CREATE [OR REPLACE ] TRIGGER trigger_name

 {BEFORE | AFTER | INSTEAD OF}: Especificação do momento do evento de execução da trigger.

 {INSERT [OR] | UPDATE [OR] | DELETE}: Operações DML a serem executadas.

 [ON table_name]: Associação de uma tabela com a trigger.

Material de Apoio – SQL 1.0

Para exemplificarmos, primeiramente criaremos uma tabela de produtos e uma de auditoria:

create table produto_uni (nome varchar2(30), descricao varchar2(30));

create table produto_aud(dt_audit date, nome varchar2(30), descricao varchar2(30));

create or replace trigger trg_prod_uni

insert into produto_uni values('som', 'aparelho de sow de 300w');

insert into produto_uni values('cd', 'pacote de cd');

insert into produto_uni values('monitor', 'monitor de 15 polegadas');

Material de Apoio – SQL 1.0

select * from produto_aud

Material de Apoio – SQL 1.0

create table cadastro_cliente (nm_cliente varchar2(30), sobrenome varchar2(30), dt_cadastro date);

create table cadastro_aud(dt_audit date, nm_cliente varchar2(30), sobrenome varchar2(30),

create or replace trigger trg_cad_clientes

insert into cadastro_cliente values('Marcelo','Diniz Augusto', sysdate);

select * from cadastro_aud;

create or replace trigger trg_cad_clientes

insert into cadastro_cliente values('Marcelo','Diniz Augusto', sysdate);

create table salario (cd_func number(10), nm_func varchar2(30), salario number(30));

create table salario_aud(dt_audit date, cd_func number(10), nm_func varchar2(30), salario

insert into salario values(1,'Marcelo Diniz', 1000);

create or replace trigger trg_func

create table dept_emp (cd_funcionario number(10), cd_filial number(10), nm_funcionario

insert into dept_emp values (1, 500, 'Marcelo Diniz', 'TI');

create or replace trigger trg_prod_uni

RAISE_APPLICATION_ERROR(-20512,'O salário não pode ser aumentado em mais de 10%');

create or replace trigger trg_func

create or replace trigger trg_dept

Material de Apoio – SQL 1.0

Você também pode gostar