terça-feira, 23 de janeiro de 2007

Gatilhos (Triggers) no Firebird

Introdução

  • Triggers são rotinas independentes (Stored Procedures especiais), que são conectadas a tabelas ou views.
  • Uma trigger é automaticamente executada ("disparada") quando um registro é inserido, atualizado ou apagado de uma tabela ou view.
  • Triggers nunca são chamadas diretamente, apenas por meio dos comandos INSERT, UPDATE ou DELETE.
  • Triggers são escritas em Linguagem de Stored Procedures e podem ser usadas Exceções.

Vantagens

  • Redução na manutenção das aplicações cliente.
  • Você pode implementar um relatório de modificações para as tabelas.
  • Generator's podem ser chamados automaticamente e seus valores atribuídos a campos.
  • Notificação automática das aplicações pelo uso de Eventos.

Sintaxe básica

CREATE [OR ALTER] TRIGGER nome FOR nomedatabela 
[ACTIVE | INACTIVE]
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
[OR {INSERT | UPDATE | DELETE}
[OR {INSERT | UPDATE | DELETE}]]
[POSITION numero]
AS <trigger_body>
  • ACTIVE/INACTIVE: Uma trigger pode ser desativada, para desenvolvimento, por exemplo. Pode ser usado com o comando ALTER TRIGGER.
  • BEFORE/AFTER: Indica ao sistema se a trigger deve ser disparada antes ou depois das operações de Insert/Delete/Update.
  • POSITION: Ordem de chamada da Trigger. As Trigger's com menor número são disparadas primeiro. Trigger's com o mesmo numero são disparadas em ordem indefinida. A posição padrão é 0 (zero).
  • trigger_body: Igual ao corpo de uma SP.

Diferenças para Stored Procedures

  • As Varáveis de contexto (OLD.colname and NEW.colname) só podem ser usadas nas trigger's
  • Trigger's não tem parâmetros IN e/ou OUT. Assim como não suportam os comandos SUSPEND e EXIT.
  • NEW.colname: Novo valor da coluna nas operações de INSERT ou UPDATE.
  • OLD.colname: Valor antigo da coluna disponível para as operações UPDATE or DELETE.

Variáveis de contexto

Nome da variável Conteúdo
OLD.field Valor do campo antes de insert/update/delete
NEW.field Novo valor para o campo em operações de INSERT ou UPDATE
INSERTING Verdadeiro se a trigger estiver sendo chamada por uma operação de INSERT
UPDATING Verdadeiro se a trigger estiver sendo chamada por uma operação de UPDATE
DELETING Verdadeiro se a trigger estiver sendo chamada por uma operação de DELETE

Alterando e apagando triggers

  • ALTER TRIGGER nome ACTIVE | INACTIVE: ativa/desativa uma trigger.
  • ALTER TRIGGER name ...: Muda a definição da trigger.
  • DROP TRIGGER name: Apaga a trigger. Não podem ser executado enquando a trigger estiver sendo executada.
  • CREATE OR ALTER TRIGGER name ...: Cria a trigger se não existir e altera se existir.

Triggers e transações

  • Trigger's rodam no contexto da sua transação, elas são tratadas como parte da operação atual.
  • As ações que são disparadas pela trigger são desfeitas quando ocorre um ROLLBACK.

Exemplos

Insere um valor de Generator para um registro recentemente adicionado

CREATE TRIGGER Create_Log_ID FOR Logs
BEFORE INSERT
AS
BEGIN
NEW.ID = GEN_ID (LogIdGenerator, 1);
END;

Log Changes to the PRICE field
CREATE TRIGGER Log_Price_Upd FOR Articles
AFTER INSERT OR UPDATE
AS
BEGIN
IF INSERTING THEN
INSERT INTO PRICE_LOGS (ART_ID, ACTION, DATETIME, NEW_PRICE)
VALUES (NEW.ART_ID, 'I', 'NOW', NEW.PRICE);
ELSE
INSERT INTO PRICE_LOGS (ART_ID, ACTION, DATETIME, NEW_PRICE)
VALUES (NEW.ART_ID, 'U', 'NOW', NEW.PRICE);
END;

Pesquisa sem considerar maiúsculas e minúsculas

Suponha que você tenha uma tabela de empregados e queira habilitar uma pesquisa pelo último nome, independente de maiúscula e minúscula:

CREATE TABLE EMPLOYEES (
  EMPNO INTEGER NOT NULL PRIMARY KEY,
  LASTNAME VARCHAR (50),

  FIRSTNAME VARCHAR (50));

Agora inclua um novo campo LASTNAME_UPPER que conterá o LASTNAME em maiúscula. Você precisa informar o COLLATION para que o UPPER() funcione corretamente.
CREATE TABLE EMPLOYEES (
 EMPNO INTEGER NOT NULL PRIMARY KEY,
 LASTNAME VARCHAR (50) COLLATE DE_DE,LASTNAME_UPPER VARCHAR (50) COLLATE DE_DE,
 FIRSTNAME VARCHAR (50) COLLATE DE_DE);

Now you need a trigger which will fill the LASTNAME_UPPER field at every INSERT or UPDATE operation. There is no need for your application to fill LASTNAME_UPPER.

CREATE OR ALTER TRIGGER BIU_EMPLOYEES FOR EMPLOYEES
BEFORE INSERT OR UPDATE
AS
BEGIN
NEW.LASTNAME_UPPER = UPPER (NEW.LASTNAME);
END;

Depois, você precisa de um índice para LASTNAME_UPPER para fazer que a pesquisa fique mais rápida:

CREATE INDEX IDX_EMPL_LASTNAME ON EMPLOYEES (LASTNAME_UPPER);

Agora você pode procurar por empregados usando um SELECT normal usando LASTNAME_UPPER ao invés de LASTNAME. Lembre-se de usar UPPER() no termo que será pesquisado:


SELECT EMPNO FROM EMPLOYEES WHERE LASTNAME_UPPER = 'HEYMANN';

Nenhum comentário:

Neobux