Após esta aula, você será capaz de:
- Usar Triggers de Linha e de Instrução
- Usar Triggers INSTEAD OF
Triggers de Linha e de Instrução
Triggers de Linha
Um trigger de linha é disparado tantas vezes quanto o número de linhas afetadas pela instrução que o ativa. Quando a cláusula FOR EACH ROW está presente na cláusula CREATE TRIGGER, o trigger é um trigger de linha. A trigger a seguir requer a tabela “salary_changes” conforme DDL abaixo:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE salary_changes ( change_id NUMBER PRIMARY KEY, employee_id NUMBER NOT NULL, change_date DATE NOT NULL, old_salary NUMBER(8,2), new_salary NUMBER(8,2), difference NUMBER(8,2) ); CREATE SEQUENCE salary_changes_seq START WITH 1 INCREMENT BY 1; |
Exemplo de um trigger de linha:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE OR REPLACE TRIGGER emp_au AFTER UPDATE ON EMPLOYEES FOR EACH ROW DECLARE v_old_salary NUMBER; v_new_salary NUMBER; v_salary_diff NUMBER; BEGIN v_old_salary := :OLD.salary; v_new_salary := :NEW.salary; v_salary_diff := v_new_salary - v_old_salary; IF v_salary_diff <> 0 THEN INSERT INTO salary_changes (employee_id, change_date, old_salary, new_salary, difference) VALUES (:NEW.employee_id, SYSDATE, v_old_salary, v_new_salary, v_salary_diff); END IF; END; / |
Neste exemplo, se uma instrução UPDATE modificar 20 registros na tabela EMPLOYEES, este trigger será disparado 20 vezes, uma vez para cada linha afetada.
Triggers de Instrução
Um trigger de instrução é disparado uma vez para a instrução que o ativa, independentemente do número de linhas afetadas. Para criar um trigger de instrução, você omite a declaração FOR EACH ROW na cláusula CREATE TRIGGER.
Antes de criar a trigger precisamos das tabelas JOB_HISTORY e HR_EVENTS, o DDL da JOB_HISTORY e HR_EVENTS está logo abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE TABLE JOB_HISTORY ( employee_id NUMBER NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, job_id VARCHAR2(10) NOT NULL, department_id NUMBER, CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date), CONSTRAINT jhist_emp_fk FOREIGN KEY (employee_id) REFERENCES EMPLOYEES(employee_id), CONSTRAINT jhist_job_fk FOREIGN KEY (job_id) REFERENCES JOBS(job_id), CONSTRAINT jhist_dept_fk FOREIGN KEY (department_id) REFERENCES DEPARTMENTS(department_id), CONSTRAINT jhist_date_interval CHECK (end_date > start_date) ); CREATE TABLE hr_events ( event_id NUMBER PRIMARY KEY, event_date DATE NOT NULL, event_type VARCHAR2(50) NOT NULL, description VARCHAR2(200) NOT NULL ); CREATE SEQUENCE hr_events_seq START WITH 1 INCREMENT BY 1; -- Insiera alguns registros na tabela JOB_HISTORY INSERT INTO JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id) VALUES (101, DATE '2020-01-01', DATE '2021-12-31', 'IT_PROG', 60); INSERT INTO JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id) VALUES (102, DATE '2019-06-01', DATE '2020-05-31', 'MK_REP', 20); -- commit COMMIT; |
Exemplo de um trigger de instrução:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR REPLACE TRIGGER job_history_ad AFTER DELETE ON JOB_HISTORY DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM job_history; INSERT INTO hr_events (event_id, event_date, event_type, description) VALUES (hr_events_seq.NEXTVAL, SYSDATE, 'DELETE', 'Registros deletados da JOB_HISTORY. Contagem restante: ' || v_count); END; / |
Agora vamos deletar um registro para verificar o funcionamento da trigger:
|
1 |
DELETE FROM JOB_HISTORY WHERE employee_id = 102; |
Este trigger específico é disparado uma vez após uma instrução DELETE ser emitida contra a tabela JOB_HISTORY, independentemente do número de linhas afetadas.
Triggers de instrução devem ser usados quando as operações realizadas pelo trigger não dependem dos dados em registros individuais. Por exemplo, se você quiser limitar o acesso a uma tabela apenas ao horário comercial, você pode usar um trigger de instrução.
Exemplo de um trigger de instrução mais complexo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE OR REPLACE TRIGGER employees_biud BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES DECLARE v_day VARCHAR2(10); v_time NUMBER; v_allowed BOOLEAN; BEGIN v_day := RTRIM(TO_CHAR(SYSDATE, 'DAY')); v_time := TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')); v_allowed := CASE WHEN v_day IN ('SATURDAY', 'SUNDAY') THEN FALSE WHEN v_time BETWEEN 9 AND 17 THEN TRUE ELSE FALSE END; IF NOT v_allowed THEN RAISE_APPLICATION_ERROR(-20000, 'Modificações na tabela EMPLOYEES são permitidas apenas em dias úteis entre 9h e 17h'); END IF; END; / |
Este trigger verifica o dia da semana e a hora antes de permitir modificações na tabela EMPLOYEES. Se a operação for tentada fora do horário permitido, uma exceção será lançada.
INSTEAD OF Triggers
Até agora, vimos triggers definidos em tabelas de banco de dados. O PL/SQL também fornece triggers que são definidos em views de banco de dados. Uma view é uma representação personalizada de dados que pode ser referida como uma “consulta armazenada”.
Exemplo de uma view:
|
1 2 3 4 5 |
CREATE OR REPLACE VIEW emp_dept_view AS SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_id, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; |
Assim como as tabelas, as views podem ser manipuladas via instruções INSERT, UPDATE e DELETE, com algumas restrições. Quando qualquer uma dessas instruções é emitida contra uma view, os dados correspondentes são modificados na tabela subjacente.
No entanto, algumas restrições são impostas às views em termos de se elas podem ser modificadas. Estas restrições se aplicam à instrução SELECT subjacente, também referida como “consulta de view”. Uma view não pode ser modificada se sua consulta contiver:
- Operações de conjunto como UNION, UNION ALL, INTERSECT e MINUS
- Funções de grupo como AVG, COUNT, MAX, MIN e SUM
- Cláusulas GROUP BY ou HAVING
- Cláusulas CONNECT BY ou START WITH
- O operador DISTINCT
- A pseudocoluna ROWNUM
Antes de criarmos uma view não-modificável vamos primeiro criar alguns objetos para usarmos nessa view. Nós vamos usar novas tabelas porque o schema HR tem muitas constraints de integridade referencial (Foreign Key) o que não faz parte do escopo desse material.
Aqui estão as DDLs de criação dos objetos:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
-- Criação das tabelas CREATE TABLE projects ( project_id NUMBER PRIMARY KEY, project_name VARCHAR2(100) NOT NULL, start_date DATE, end_date DATE, budget NUMBER(10,2) ); CREATE TABLE employees_new ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, email VARCHAR2(100) UNIQUE, hire_date DATE, salary NUMBER(8,2), manager_id NUMBER, CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees_new(employee_id) ); CREATE TABLE project_allocations ( allocation_id NUMBER PRIMARY KEY, employee_id NUMBER, project_id NUMBER, allocation_start_date DATE, allocation_end_date DATE, role VARCHAR2(50), CONSTRAINT fk_alloc_emp FOREIGN KEY (employee_id) REFERENCES employees_new(employee_id), CONSTRAINT fk_alloc_proj FOREIGN KEY (project_id) REFERENCES projects(project_id) ); -- Nova tabela hr_events_new CREATE TABLE hr_events_new ( event_id NUMBER PRIMARY KEY, event_date DATE, event_type VARCHAR2(50), description VARCHAR2(4000) ); CREATE SEQUENCE hr_events_new_seq START WITH 1 INCREMENT BY 1; -- Inserção de dados de exemplo INSERT INTO projects (project_id, project_name, start_date, end_date, budget) VALUES (1, 'Project Alpha', DATE '2023-01-01', DATE '2023-12-31', 100000); INSERT INTO projects (project_id, project_name, start_date, end_date, budget) VALUES (2, 'Project Beta', DATE '2023-02-01', DATE '2023-11-30', 150000); INSERT INTO employees_new (employee_id, first_name, last_name, email, hire_date, salary, manager_id) VALUES (1, 'John', 'Doe', 'john.doe@example.com', DATE '2022-01-15', 50000, NULL); INSERT INTO employees_new (employee_id, first_name, last_name, email, hire_date, salary, manager_id) VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', DATE '2022-02-01', 55000, 1); INSERT INTO employees_new (employee_id, first_name, last_name, email, hire_date, salary, manager_id) VALUES (3, 'Bob', 'Johnson', 'bob.johnson@example.com', DATE '2022-03-10', 52000, 1); INSERT INTO project_allocations (allocation_id, employee_id, project_id, allocation_start_date, allocation_end_date, role) VALUES (1, 1, 1, DATE '2023-01-01', DATE '2023-06-30', 'Project Manager'); INSERT INTO project_allocations (allocation_id, employee_id, project_id, allocation_start_date, allocation_end_date, role) VALUES (2, 2, 1, DATE '2023-01-01', DATE '2023-12-31', 'Developer'); INSERT INTO project_allocations (allocation_id, employee_id, project_id, allocation_start_date, allocation_end_date, role) VALUES (3, 3, 2, DATE '2023-02-01', DATE '2023-11-30', 'Analyst'); commit; |
E agora finalmente, um exemplo de uma view não modificável:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR REPLACE VIEW employee_project_summary AS SELECT e.employee_id, e.first_name, e.last_name, e.email, e.salary, e.manager_id, COUNT(DISTINCT pa.project_id) AS project_count, SUM(p.budget) AS total_project_budget FROM employees_new e LEFT JOIN project_allocations pa ON e.employee_id = pa.employee_id LEFT JOIN projects p ON pa.project_id = p.project_id GROUP BY e.employee_id, e.first_name, e.last_name, e.email, e.salary, e.manager_id; |
Esta view não é atualizável porque contém a função de grupo COUNT(). Tentar deletar desta view resultaria em um erro:
|
1 2 |
DELETE FROM employee_project_summary WHERE employee_id = 1; -- Resulta em: ORA-01732: data manipulation operation not legal on this view |
Para contornar essas limitações, o PL/SQL fornece triggers INSTEAD OF. Um trigger INSTEAD OF é criado como um trigger de linha e é disparado em vez da instrução de ativação (INSERT, UPDATE, DELETE) que foi emitida contra uma view, permitindo que você modifique diretamente as tabelas subjacentes.
Exemplo de um trigger INSTEAD OF:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
CREATE OR REPLACE TRIGGER employee_project_summary_delete INSTEAD OF DELETE ON employee_project_summary FOR EACH ROW DECLARE v_is_manager NUMBER; v_allocation_count NUMBER; BEGIN -- Verifica se o funcionário é um gerente SELECT COUNT(*) INTO v_is_manager FROM employees_new WHERE manager_id = :OLD.employee_id; -- Verifica se o funcionário está alocado em algum projeto SELECT COUNT(*) INTO v_allocation_count FROM project_allocations WHERE employee_id = :OLD.employee_id; -- Se for um gerente, atualiza os subordinados IF v_is_manager > 0 THEN UPDATE employees_new SET manager_id = NULL WHERE manager_id = :OLD.employee_id; END IF; -- Remove as alocações de projeto do funcionário DELETE FROM project_allocations WHERE employee_id = :OLD.employee_id; -- Remove o funcionário DELETE FROM employees_new WHERE employee_id = :OLD.employee_id; -- Registra a ação na nova tabela hr_events_new INSERT INTO hr_events_new (event_id, event_date, event_type, description) VALUES (hr_events_new_seq.NEXTVAL, SYSDATE, 'DELETE', 'Funcionário ' || :OLD.employee_id || ' removido. ' || CASE WHEN v_is_manager > 0 THEN 'Era gerente. Subordinados atualizados. ' ELSE '' END || 'Removido de ' || v_allocation_count || ' alocações de projetos.'); END; / |
Explicação Detalhada:
- Estrutura de Dados:
- Criamos tabelas para
projects,employees_new, eproject_allocations, que formam a base do nosso sistema. - A nova tabela
hr_events_newé usada para registrar eventos de RH, substituindo a antigahr_events. Isso porque a estrutura original do schema HR contém várias constraints de integridade que teriam que ser tratadas antes da execução.
- Criamos tabelas para
- View Complexa:
employee_project_summaryé uma view não modificável diretamente devido ao uso de funções de agregação (COUNT, SUM) e cláusula GROUP BY.- Esta view combina informações de funcionários com seus projetos e orçamentos totais.
- INSTEAD OF Trigger:
- O trigger
employee_project_summary_deleteé acionado quando uma operação DELETE é tentada na viewemployee_project_summary. - Este trigger é necessário porque a view não é diretamente modificável devido à sua complexidade.
- O trigger
- Lógica do Trigger:
a) Verifica se o funcionário a ser deletado é um gerente.
b) Conta o número de projetos aos quais o funcionário está alocado.
c) Se o funcionário for um gerente, atualiza os subordinados, removendo a referência ao gerente.
d) Remove todas as alocações de projetos do funcionário.
e) Remove o funcionário da tabela
employees_new.f) Registra a ação na nova tabela
hr_events_new, incluindo detalhes sobre se era um gerente e quantas alocações de projetos foram removidas. - Logging e Auditoria:
- Todas as ações são registradas na tabela
hr_events_new, proporcionando uma trilha de auditoria clara.
- Todas as ações são registradas na tabela
Para testar o trigger, você pode executar:
|
1 |
DELETE FROM employee_project_summary WHERE employee_id = 1; |
Este comando acionará o trigger, que realizará todas as operações necessárias nas tabelas subjacentes e registrará o evento na tabela hr_events_new.
Este INSTEAD OF trigger demonstra como podemos manter a integridade dos dados e realizar operações complexas em cascata, mesmo quando trabalhamos com views não modificáveis diretamente. É uma técnica poderosa para gerenciar dados em sistemas complexos, permitindo operações que de outra forma seriam impossíveis em views agregadas ou complexas.
Considerações Adicionais sobre INSTEAD OF Triggers
Ao trabalhar com INSTEAD OF triggers, é importante entender as implicações de suas ações em um ambiente de banco de dados complexo. Vamos explorar um exemplo mais detalhado para ilustrar alguns desafios potenciais:
Suponha que temos uma view mais complexa que combina informações de várias tabelas:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE OR REPLACE VIEW employee_details_view AS SELECT e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, d.department_id, d.department_name, l.city, l.state_province, c.country_name, j.job_title FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id JOIN countries c ON l.country_id = c.country_id JOIN jobs j ON e.job_id = j.job_id; |
Agora, vamos criar um INSTEAD OF trigger para lidar com operações de DELETE nesta view:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
CREATE OR REPLACE TRIGGER employee_details_delete INSTEAD OF DELETE ON employee_details_view FOR EACH ROW DECLARE v_emp_count NUMBER; v_dept_count NUMBER; BEGIN -- Primeiro, removemos o empregado DELETE FROM employees WHERE employee_id = :OLD.employee_id; -- Verificamos se o departamento ficou vazio SELECT COUNT(*) INTO v_emp_count FROM employees WHERE department_id = :OLD.department_id; IF v_emp_count = 0 THEN -- Se o departamento está vazio, podemos removê-lo DELETE FROM departments WHERE department_id = :OLD.department_id; -- Agora, verificamos se a localização ficou sem departamentos SELECT COUNT(*) INTO v_dept_count FROM departments WHERE location_id = (SELECT location_id FROM departments WHERE department_id = :OLD.department_id); IF v_dept_count = 0 THEN -- Se a localização está sem departamentos, podemos removê-la DELETE FROM locations WHERE location_id = (SELECT location_id FROM departments WHERE department_id = :OLD.department_id); -- Nota: Não estamos removendo países, pois isso poderia ter implicações muito amplas END IF; END IF; -- Registramos a ação INSERT INTO hr_events (event_date, event_type, description) VALUES (SYSDATE, 'DELETE', 'Empregado ' || :OLD.employee_id || ' removido. Verificações em cascata realizadas.'); EXCEPTION WHEN OTHERS THEN -- Registramos qualquer erro que ocorra INSERT INTO hr_events (event_date, event_type, description) VALUES (SYSDATE, 'ERROR', 'Erro ao tentar remover empregado ' || :OLD.employee_id || ': ' || SQLERRM); -- Re-lançamos a exceção RAISE; END; / |
Este trigger INSTEAD OF é mais complexo e ilustra vários pontos importantes:
- Operações em Cascata: O trigger não apenas remove o empregado, mas também verifica e potencialmente remove departamentos e localizações vazias. Isso demonstra como um INSTEAD OF trigger pode gerenciar operações complexas em cascata que envolvem múltiplas tabelas.
- Considerações de Integridade: O trigger toma cuidado para não remover países, mesmo se uma localização for removida. Isso ilustra a necessidade de considerar cuidadosamente as implicações de cada ação em um ambiente de banco de dados complexo.
- Tratamento de Erros: O trigger inclui tratamento de exceções para capturar e registrar quaisquer erros que possam ocorrer durante a execução.
- Logging: O trigger registra suas ações em uma tabela de eventos, o que é uma prática útil para auditoria e depuração.
Desafios e Considerações
Ao projetar INSTEAD OF triggers, especialmente para views complexas, é importante considerar:
- Desempenho: Triggers complexos que realizam muitas operações podem impactar o desempenho do banco de dados.
- Integridade dos Dados: Certifique-se de que todas as operações em cascata mantenham a integridade dos dados e respeitem as restrições de chave estrangeira.
- Concorrência: Em um ambiente multi-usuário, considere como as operações do trigger podem interagir com outras transações concorrentes.
- Manutenção: Triggers complexos podem ser difíceis de manter e depurar. Documente bem o propósito e a lógica do trigger.
- Teste: Teste exaustivamente os triggers INSTEAD OF para garantir que eles lidem corretamente com todos os cenários possíveis.
Conclusão
INSTEAD OF triggers são uma ferramenta poderosa que permite operações aparentemente diretas em views complexas. Eles podem encapsular lógica de negócios complexa e manter a integridade dos dados em várias tabelas relacionadas. No entanto, eles devem ser usados com cuidado e projetados com consideração às relações entre tabelas, desempenho e manutenção futura.
Ao usar INSTEAD OF triggers, você está essencialmente criando uma camada de abstração entre a view e as tabelas subjacentes, permitindo que os usuários interajam com os dados de uma maneira que parece simples e direta, enquanto operações complexas ocorrem nos bastidores.
Esta abordagem pode ser muito útil para simplificar interfaces de usuário ou APIs, ao mesmo tempo em que mantém a flexibilidade e o controle sobre como as operações são realmente executadas no nível do banco de dados.
