PL / SQL - Triggers

Neste capítulo, discutiremos Triggers em PL / SQL. Os gatilhos são programas armazenados, que são executados ou disparados automaticamente quando ocorrem alguns eventos. Os gatilhos são, na verdade, escritos para serem executados em resposta a qualquer um dos seguintes eventos -

UMA database manipulation (DML) declaração (DELETE, INSERT ou UPDATE)
UMA database definition (DDL) instrução (CREATE, ALTER ou DROP).
UMA database operation (SERVERERROR, LOGON, LOGOFF, STARTUP ou SHUTDOWN).

Os gatilhos podem ser definidos na tabela, visão, esquema ou banco de dados ao qual o evento está associado.

Benefícios dos gatilhos

Os gatilhos podem ser escritos para os seguintes fins -

Gerando alguns valores de coluna derivados automaticamente
Impondo integridade referencial
Registro de eventos e armazenamento de informações sobre o acesso à mesa
Auditing
Replicação síncrona de tabelas
Impondo autorizações de segurança
Prevenir transações inválidas

Criação de gatilhos

A sintaxe para criar um gatilho é -

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;

Onde,

CREATE [OR REPLACE] TRIGGER trigger_name - Cria ou substitui um trigger existente pelo trigger_name .
{ANTES | DEPOIS | INSTEAD OF} - Especifica quando o gatilho será executado. A cláusula INSTEAD OF é usada para criar gatilhos em uma visualização.
{INSERT [OU] | ATUALIZAR [OU] | DELETE} - Isso especifica a operação DML.
[OF nome_coluna] - Especifica o nome da coluna que será atualizado.
[ON nome_tabela] - Especifica o nome da tabela associada ao acionador.
[REFERENCING OLD AS o NEW AS n] - Isso permite que você consulte valores novos e antigos para várias instruções DML, como INSERT, UPDATE e DELETE.
[PARA CADA LINHA] - Especifica um acionador no nível da linha, ou seja, o acionador será executado para cada linha afetada. Caso contrário, o gatilho será executado apenas uma vez quando a instrução SQL for executada, o que é chamado de gatilho de nível de tabela.
WHEN (condição) - fornece uma condição para as linhas nas quais o gatilho seria disparado. Esta cláusula é válida apenas para gatilhos em nível de linha.

Exemplo

Para começar, usaremos a tabela CLIENTES que criamos e usamos nos capítulos anteriores -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

O programa a seguir cria um row-levelgatilho para a tabela de clientes que seria acionado para operações INSERT, UPDATE ou DELETE executadas na tabela CUSTOMERS. Este gatilho exibirá a diferença salarial entre os valores antigos e os novos -

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Trigger created.

Os seguintes pontos devem ser considerados aqui -

Referências OLD e NEW não estão disponíveis para gatilhos de nível de tabela, em vez disso, você pode usá-las para gatilhos de nível de registro.
Se você deseja consultar a tabela no mesmo gatilho, deve usar a palavra-chave AFTER, porque os gatilhos podem consultar a tabela ou alterá-la novamente somente após as alterações iniciais serem aplicadas e a tabela voltar a um estado consistente.
O gatilho acima foi escrito de forma a disparar antes de qualquer operação DELETE, INSERT ou UPDATE na tabela, mas você pode escrever seu gatilho em uma ou várias operações, por exemplo, BEFORE DELETE, que será acionado sempre que houver um registro será excluído usando a operação DELETE na tabela.

Disparando um gatilho

Vamos realizar algumas operações DML na tabela CUSTOMERS. Aqui está uma instrução INSERT, que criará um novo registro na tabela -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

Quando um registro é criado na tabela CLIENTES, o gatilho de criação acima, display_salary_changes será disparado e exibirá o seguinte resultado -

Old salary: 
New salary: 7500 
Salary difference:

Por se tratar de um novo registro, o salário anterior não está disponível e o resultado acima é nulo. Vamos agora realizar mais uma operação DML na tabela CUSTOMERS. A instrução UPDATE atualizará um registro existente na tabela -

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2;

Quando um registro é atualizado na tabela CUSTOMERS, o gatilho de criação acima, display_salary_changes será disparado e exibirá o seguinte resultado -

Old salary: 1500 
New salary: 2000 
Salary difference: 500