PL/SQL – Triggers

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

• p>p> uma declaração de manipulação de base de dados (DML) (DELETE, INSERT, ou UPDATE)

• uma declaração de definição de base de dados (DDL) (CREATE, ALTER, ou DROP).

• p>uma operação de base de dados (SERVERROR, LOGON, LOGOFF, STARTUP, ou SHUTDOWN).

Triggers podem ser definidos na tabela, vista, esquema, ou base de dados com a qual o evento está associado.

Benefícios dos Triggers

Triggers podem ser escritos para os seguintes fins –

• Gerando automaticamente alguns valores derivados da coluna
• Enforçando a integridade referencial
• Evite o registo e armazenamento de informação no acesso à tabela
• Auditoria
• Replicação síncrona de tabelas
• Imprimir autorizações de segurança
• Prevenir transacções inválidas

Criar gatilhos

A sintaxe para criar um gatilho é –

CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT | UPDATE | DELETE} ON table_name WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; 

Onde

• CREATE TRIGGER trigger_name – Cria ou substitui um trigger_name existente pelo trigger_name.

• {ANTES | DEPOIS | INSTEAD OF} – Isto especifica quando é que o gatilho será executado. A cláusula INSTEAD OF é utilizada para criar o gatilho numa view.

• {INSERT | UPDATE | DELETE} – Isto especifica a operação DML.

• – Isto especifica o nome da coluna que será actualizada.

• – Isto especifica o nome da tabela associada ao gatilho.

• – Isto permite referir valores novos e antigos para várias declarações DML, tais como INSERT, UPDATE, e DELETE.

• – Isto especifica um gatilho a nível de linha, ou seja o gatilho será executado para cada linha a ser afectada. Caso contrário, o gatilho será executado apenas uma vez quando a instrução SQL for executada, o que se chama gatilho a nível de tabela.

• WHEN (condição) – Isto fornece uma condição para as linhas para as quais o gatilho dispararia. Esta cláusula é válida apenas para os gatilhos de nível de linha.

Exemplo

Para começar, vamos utilizar a tabela CLIENTES que tínhamos criado e utilizado 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 seguinte cria um gatilho a nível de linha para a tabela de clientes que dispararia para as operações INSERT ou UPDATE ou DELETE realizadas na tabela CLIENTES. Este gatilho exibirá a diferença salarial entre os valores antigos e os novos valores –

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, produz o seguinte resultado –

Trigger created.

Os seguintes pontos têm de ser considerados aqui –

• p>P> OURO e NOVAS referências não estão disponíveis para gatilhos a nível de tabela, em vez disso pode utilizá-las para gatilhos a nível de registo.
• p> Se quiser consultar a tabela no mesmo trigger, então deve usar a palavra-chave DEPOIS, porque os triggers podem consultar a tabela ou alterá-la novamente apenas após as alterações iniciais serem aplicadas e a tabela estar de novo num estado consistente.
• p> O gatilho acima foi escrito de modo a disparar antes de qualquer operação DELETE ou INSERT ou UPDATE na tabela, mas pode escrever o gatilho numa única ou múltiplas operações, por exemplo ANTES de DELETE, que disparará sempre que um registo for apagado usando a operação DELETE na tabela.

Acionar um gatilho

Deixe-nos realizar algumas operações DML na tabela CLIENTES. Aqui está uma declaração INSERT, que irá criar um novo registo na tabela –

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

Quando um registo é criado na tabela CLIENTES, o acima referido cria um gatilho, display_salary_changes será despedido e exibirá o seguinte resultado –

Old salary: New salary: 7500 Salary difference:

Porque este é um novo registo, o salário antigo não está disponível e o resultado acima vem como nulo. Vamos agora realizar mais uma operação de DML na tabela CLIENTES. A declaração UPDATE actualizará um registo existente na tabela –

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

Quando um registo é actualizado na tabela CLIENTES, o acima exposto cria o gatilho, display_salary_changes será despedido e exibirá o seguinte resultado –

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