stored procedures e triggers no firebird

8/7/2019 stored procedures e triggers no firebird

1/19

Introduo a StoredProcedures e Triggers no Firebird

Por Bill Todd, Borland Developers Conference San Diego 2000

Traduzido e adaptado com autorizao do autor por:

Alessandro Cunha Fernandes, Comunidade Firebird, Julho de 2002

Uma stored procedure um programa escrito numa linguagem prpria para procedures etriggers do Firebird que armazenado como parte do banco de dados. Storedprocedurespodem ser chamadas por aplicaes cliente ou por outras stored procedures ou triggers.Triggers so quase a mesma coisa que stored procedures exceto pelo modo como sochamadas. Triggersso chamadas automaticamente quando uma alterao em uma linha databela ocorre. Este artigo examina primeiro as storedprocedurese logo depois as triggers.Como voc poder ver a maioria das coisas que sero ditas sobre stored procedures seaplicaro tambm s triggers.

Vantagens do uso de StoredProcedures

A maior vantagem do uso de stored procedures a reduo de trfico na rede. J que asstored proceduresso executadas pelo Firebird na mquina servidora de banco de dados,voc pode utiliza-las para mover grande parte do seu cdigo de manipulao de dados parao servidor. Isto elimina a transferncia de dados do servidor para o cliente, pela rede, para amanipulao e reduzir trfico aumentar performance, particularmente em uma WAN ou emqualquer conexo de baixa velocidade.

Stored procedures aumentam a performance de outra forma tambm. Voc pode utilizarquerys para fazer muitas das coisas que podem ser feitas com stored proceduresmas uma

query tem uma grande desvantagem. Cada vez que a aplicao cliente envia um comandoSQL para o servidor o comando tem que ser parsed, ou seja, analisado gramaticalmente,submetido ao optimizador para formulao de um plano de execuo. Stored proceduressoanalisadas , optimizadas e armazenadas em uma forma executvel no momento em que soadicionadas ao banco de dados. A partir do momento que uma stored procedureno temque ser analisada e optimizada cada vez que chamada, ela executada mais rapidamenteque uma query equivalente. Stored procedures podem tambm executar operaes muitomais complexas que uma simples query.

Se mais de uma aplicao ir acessar o banco de dados, as stored procedures podemtambm economizar tempo de manuteno e desenvolvimento j que qualquer aplicaopoder chama-la. A manuteno mais fcil porque voc pode alterar a stored proceduresem ter que alterar ou mesmo recompilar cada aplicao cliente.

Finalmente, stored procedures tem uma grande importncia na segurana do banco dedados uma vez que elas podem acessar tabelas que o usurio no tem o direito de faze-lo.Por exemplo, suponha que um usurio precise rodar um relatrio que mostra o total desalrios por departamento e nvel salarial. Embora estas informaes venham da tabela desalrios dos empregados voc no quer que este usurio tenha acesso aos salriosindividuais de todos os empregados. A soluo escrever uma stored procedureque extraiada tabela de salrios as informaes resumidas que o relatrio precisa e dar direitos de


2/19

leitura stored procedurepara a tabela de salrios. Voc pode ento dar direito ao usuriode executar a stored procedure. O usurio no precisa ter direitos sobre a tabela de salrios.

Quando voc deve usar StoredProcedures?

A resposta curta , sempre que voc puder. No existem desvantagens em se usar storedprocedures. Existem apenas duas limitaes. Primeiro, voc tem que ser capaz de passarqualquer informao varivel para a stored procedure como parmetros ou coloca-las em

uma tabela que a stored procedure possa acessar. Segundo, a linguagem de escrita destored procedurese triggerspode ser muito limitada para operaes mais complexas.

Usando o comando CREATE PROCEDURE

Stored procedures so criadas atravs do comando CREATE PROCEDURE que tem aseguinte sintaxe:CREATE PROCEDURE NomedaProcedure

RETURNS

AS

BEGIN

END

Os parmetros de entrada permitem aplicao cliente passar os valores que sero usadospara modificar o comportamento da stored procedure. Por exemplo, se o objetivo da storedprocedure calcular o total mensal da folha de pagamento para a um determinadodepartamento, o nmero do departamento dever ser passado para a stored procedurecomoum parmetro de entrada. Parmetros de sada ou de retorno so o meio pelo qual astored procedureretorna informaes para a aplicao cliente. Em nosso exemplo, o total da

folha de pagamento mensal para o departamento passado dever ser retornado em umparmetro de sada. Um parmetro pode ser de qualquer tipo de dados do Firebird excetoBLOB ou ARRAY. A procedure a seguir demonstra o uso tanto dos parmetros de entradacomo os de sada:CREATE PROCEDURE SUB_TOT_BUDGET(

HEAD_DEPT CHAR(3)

)

RETURNS (

TOT_BUDGET NUMERIC (15, 2),

AVG_BUDGET NUMERIC (15, 2),

MIN_BUDGET NUMERIC (15, 2),

MAX_BUDGET NUMERIC (15, 2)

)AS

BEGIN

SELECT SUM(BUDGET),

AVG(budget), MIN(budget), MAX(budget)

FROM department

WHERE head_dept = :head_dept

INTO :tot_budget, :avg_budget, :min_budget, :max_budget;

SUSPEND;

END ^


3/19

Esta stored proceduredeclara um parmetro de entrada, HEAD_DEPT cujo tipo CHAR(3) equatro parmetros de sada, TOT_BUDGET, AVG_BUDGET, MIN_BUDGET, eMAX_BUDGET todos do tipo NUMERIC(15, 2). Tanto os parmetros de entrada quando osde sada devem estar entre parnteses . O comando SUSPEND pausa a stored procedureat que o cliente busque os valores dos parmetros de sada. Este comando explicado emmais detalhes mais tarde neste mesmo artigo.

Declarando variveis locais

Voc pode declarar variveis locais de qualquer tipo suportado pelo Firebird dentro de umastored procedure. Estas variveis s existem enquanto a stored procedure est sendoexecutada e seu escopo local procedure. Note que no existem variveis globais quandose trabalha com stored procedures e triggers e elas no so necessrias. Se voc temvalores que precisam ser compartilhados por duas ou mais procedures, voc pode passa-lospor parmetros ou guarda-los em uma tabela.

Variveis locais so declaradas depois da palavra chave AS e antes da palavra chaveBEGIN que identifica o incio do corpo da stored procedure. Para declarar variveis useDECLARE VARIABLE

DECLARE VARIABLE OrderCount Integer;DECLARE VARIABLE TotalAmount NUMERIC(15,2);

Note que cada comando DECLARE VARIABLE s pode declarar uma varivel. A procedurea seguir ilustra o uso do comando DECLARE VARIABLE. Ela declara quatro variveis locais ,ord_stat, hold_stat, cust_no and any_po. Observe que quando uma varivel usada naclausula INTO de um comando SELECT um sinal de dois pontos ':' deve ser adicionadocomo primeiro caracter do nome da varivel, entretanto quando a varivel usada emqualquer outra parte este sinal no mais necessrio.CREATE PROCEDURE SHIP_ORDER(

PO_NUM CHAR(8)

)

ASDECLARE VARIABLE ord_stat CHAR(7);

DECLARE VARIABLE hold_stat CHAR(1);

DECLARE VARIABLE cust_no INTEGER;

DECLARE VARIABLE any_po CHAR(8);

BEGIN

SELECT s.order_status, c.on_hold, c.cust_no

FROM sales s, customer c

WHERE po_number = :po_num

AND s.cust_no = c.cust_no

INTO :ord_stat, :hold_stat, :cust_no;

/* Este pedido j foi enviado */

IF (ord_stat = 'shipped') THENBEGIN

EXCEPTION order_already_shipped;

SUSPEND;

END

/* Cliente est em atraso. */

ELSE IF (hold_stat = '*') THEN

BEGIN

EXCEPTION customer_on_hold;

SUSPEND;


4/19

END

/*

* Se existe uma conta no paga de pedidos enviados a mais de 2 meses,

* passe o cliente para cliente em atraso.

*/

FOR SELECT po_number

FROM sales

WHERE cust_no = :cust_no

AND order_status = 'shipped'

AND paid = 'n'

AND ship_date < CAST('NOW' AS DATE) - 60

INTO :any_po

DO

BEGIN

EXCEPTION customer_check;

UPDATE customer

SET on_hold = '*'

WHERE cust_no = :cust_no;

SUSPEND;

END

/*

* Envia o pedido.*/

UPDATE sales

SET order_status = 'shipped', ship_date = 'NOW'

WHERE po_number = :po_num;

SUSPEND;

END ^

Escrevendo o corpo da procedure

O corpo da stored procedureconsiste em um conjunto de qualquer nmero de comandos da

linguagem de escrita de stored procedure e triggers do Firebird dentro de um blocoBEGIN/END. O corpo da seguinte procedure consiste em um comando SELECT e umSUSPEND entre as palavras chave BEGIN e AND.CREATE PROCEDURE SUB_TOT_BUDGET(

HEAD_DEPT CHAR(3)

)

RETURNS (

TOT_BUDGET NUMERIC (15,2),

AVG_BUDGET NUMERIC (15,2),

MIN_BUDGET NUMERIC (15,2),

MAX_BUDGET NUMERIC (15,2)

)

AS

BEGIN

SELECT SUM(budget), AVG(budget),MIN(budget), MAX(budget)

FROM department

WHERE head_dept=:head_dept

INTO :tot_budget, :avg_budget,:min_budget, :max_budget;

SUSPEND;

END ^

Cada comando no corpo de uma proceduretem que terminar com um ponto e virgula ';'.


5/19

Outros elementos de linguagem

A linguagem de escrita de stored proceduree triggersdo Firebird inclui todas as construesde uma linguagem de programao estruturada assim como declaraes prprias paratrabalhar com dados em tabelas. A seguinte seo descrever estes elementos.

Comentrios

Voc pode colocar comentrios onde quiser em uma stored procedureusando a sintaxe /*

Este um comentrio*/. Um comentrio pode ter vrias linhas, mas comentrios aninhadosno so permitidos.

Bloco de comandos (BEGIN-END)

A linguagem de stored procedures e triggers se assemelha ao Pascal em algumasconstrues como IF-THEN-ELSE e loops WHILE que somente podem conter um comando.Entretanto, as palavras chave BEGIN e END podem ser usadas para agrupar uma srie decomandos de forma que eles se tornem um comando composto. Nunca coloque um ponto-e-vrgula aps um BEGIN ou um END.

Comandos de atribuio

A linguagem de procedurese triggerssuporta comandos de atribuio da seguinte forma:Var1 = Var2 * Var3;

Var1 tanto pode ser uma varivel local quanto um parmetro de sada. Var2 e Var3 tantopodem ser variveis locais como parmetros de entrada. A expresso direita do sinal deigual pode ser to complexa quanto voc deseje e voc pode usar parnteses para agruparoperaes com quantos nveis quiser.

IF-THEN-ELSE

A sintaxe do comando IF no Firebird a seguinte:IF THEN

ELSE

Onde pode ser tanto um comando simples quanto um bloco de comandosdelimitado por um BEGIN-END. Na alm dos operadores lgicosnormais (=, , =, ) voc pode usar tambm os seguintes operadores SQL:

Expresso Condicional Descrio

Valor BETWEEN valor AND valor Faixa de valores


6/19

Valor LIKE valor O valor direita pode incluir um ou mais curingas. Use% para zero ou mais caracteres e _ para um caracter.

Valor IN (valor1, valor2, valor3, &) Membro de uma lista de valores.

Valor EXISTS (subquery) Verdadeiro se o valor combinar com um dos valoresretornados pela subquery.

Valor ANY (subquery) Verdadeiro se o valor combinar com qualquer das linhasretornadas pela subquery.

Valor ALL (subquery) Verdadeiro se o valor combinar com todas as linhasretornadas pela subquery.

Valor IS NULL Verdadeiro se o valor for nulo.

Valor IS NOT NULL Verdadeiro se o valor no for nulo.

Valor CONTAINING valor Busca de substring sem diferenciar maisculas eminsculas.

Valor STARTING WITH valor Verdadeiro se o valor a esquerda iniciar com o valor adireita. Diferencia maisculas e minsculas.


7/19

Exemplos de comandos IF vlidos so:IF

(any_sales > 0) THEN

BEGIN

EXCEPTION reassign_sales;

SUSPEND;

END

IF

(first IS NOT NULL) THEN

line2=first || ' ' || last;ELSE

line2=last;

Note no exemplo acima que na linguagem de escrita de stored procedures e triggers noFirebird o operador de concatenao de strings || (Duas barras verticais) e no o + comoacontece na maioria das linguagens de programao.

IF (:mngr_no IS NULL) THEN

BEGIN

mngr_name='--TBH--';

title='';

ENDELSE

SELECT full_name, job_code

FROM employee

WHERE emp_no=:mngr_no

INTO :mngr_name, :title;

WHILE-DO

A estrutura WHILE-DO permite criar loops nas stored procedurese triggers. A sintaxe :WHILE (


8/19

CREATE PROCEDURE ADD_EMP_PROJ(

EMP_NO SMALLINT,

PROJ_ID CHAR(5)

)

AS

BEGIN

BEGIN

INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);

WHEN SQLCODE -530 DO

EXCEPTION unknown_emp_id;END

SUSPEND;

END ^

A Segunda diferena a adio da clusula INTO ao comando SELECT de modo que vocpossa selecionar valores diretamente para variveis ou parmetros de sada como mostradono exemplo a seguir:

CREATE PROCEDURE CUSTOMER_COUNT

RETURNS (

CUSTOMERCOUNT INTEGER

)AS

BEGIN

SELECT COUNT(*) FROM CUSTOMER INTO :CustomerCount;

SUSPEND;

END ^

Voc no pode usar comandos SQL DDL em uma stored procedure. Esta restrio se aplicaaos comandos CREATE, ALTER, DROP, SET, GRANT, REVOKE, COMMIT e ROLLBACK.

Usando FOR SELECT e DO

O exemplo anterior do comando SELECT que seleciona um ou mais valores para umavarivel bom desde que o SELECT retorne apenas uma linha. Quando precisar processarvarias linhas retornadas por um SELECT voc dever usar o comando FOR SELECT e DOcomo mostrado a seguir:CREATE PROCEDURE ORDER_LIST(

CUST_NO INTEGER

)

RETURNS (

PO_NUMBER CHAR(8)

)

AS

BEGINFOR SELECT PO_NUMBER FROM SALES

WHERE CUST_NO=:CUST_NO

INTO :PO_NUMBER

DO

SUSPEND;

END ^

Esta procedure pega um cdigo de cliente de seu parmetro de entrada e retorna osnmeros de todas as compras do cliente da tabela SALES (vendas). Observe que os


9/19

nmeros das vendas so todos retornados atravs de uma nica varivel de sada. Vejacomo isso funciona: A palavra chave FOR diz paro o Firebird abrir um cursor no conjunto deresultados (result set) do comando SELECT. O comando SELECT tem que incluir a clusulaINTO que atribui cada campo retornado pelo SELECT a uma varivel local ou parmetro desada. O comando aps a palavra chave DO executado para cada linha retornada peloSELECT. O comando aps o DO pode ser um bloco de comandos delimitado por um BEGIN-END.

Usando o SUSPEND

No exemplo acima, o comando SUSPEND diz para a stored procedure suspender aexecuo at que uma solicitao de dados (fetch) seja recebida do cliente ento aprocedure l o primeiro PO_NUMBER para o parmetro de sada e o retorna para o cliente.Cada vez que o cliente emite uma requisio, o prximo PO_NUMBER lido para oparmetro de sada e retornado para o cliente. Isso continua at que todas as linhasretornadas pelo SELECT tenham sido processadas. SUSPEND no s usado com FORSELECT. Ele usado sempre que a stored procedure retorna um valor para o clienteevitando que a stored proceduretermine antes que o cliente tenha pego o resultado. A seguirum exemplo muito simples de uma procedure que retorna um valor em um parmetro desada:

CREATE PROCEDURE CUSTOMER_COUNTRETURNS (


)

AS

BEGIN


SUSPEND;

END ^

Criando e modificando StoredProcedures

O mtodo normal de se criar um banco de dados e seus objetos no Firebird criar um scriptSQL no IBConsole ou em um editor de textos e ento usar o IBConsole para executa-lo. Istocria um problema j que tanto o IBConsole como a stored procedureusam o ponto-e-vrgulapara terminar um comando.

Lidando com o dilema do ponto-e-vrgula

O IBConsole identifica o fim de cada comando em um script SQL pelo caracter de trmino decomando que por default o ponto-e-vrgula. Isto funciona bem na maioria dos casos masno na criao de stored procedures ou triggers. O problema que queremos que oIBConsole execute o comando CREATE PROCEDURE como um nico comando e isso

significa que ele deveria terminar com um ponto-e-vrgula. Entretanto, cada um doscomandos no corpo da procedure que se est criando tambm termina com um ponto-e-vrgula e o IBConsole acha que encontrou o fim do CREATE PROCEDURE quando eleencontra o primeiro ponto-e-vrgula. A nica soluo trocar o caracter de trmino, que oIBConsole procura para identificar o fim do comando, por um outro diferente do ponto-e-vrgula. Para isso usamos o comando SET TERM. O script a seguir demonstra como:


10/19

SET TERM ^ ;

CREATE PROCEDURE Customer_Count

RETURNS (

CustomerCount Integer

)

AS

BEGIN

SELECT COUNT(*) FROM CUSTOMER

INTO :CustomerCount;

SUSPEND;

END ^

SET TERM ; ^

O primeiro comando SET TERM altera o caracter de trmino de comando para o caracter (^).Note que este comando ainda tem que terminar com um ponto-e-vrgula j que este aindaser o caracter de trmino at que o SET TERM ^ seja executado. O IBConsole ir agoraignorar os ponto-e-vrgula no final dos comandos no corpo da procedures. Um (^) colocadologo aps o END final no comando CREATE PROCEDURE. Quando o IBConsole encontraeste caracter ele processa todo o comando CREATE PROCEDURE. O ltimo SET TERMvolta o terminador para o ponto-e-vrgula.

Apagando e alterando StoredProcedures

Para remover uma stored procedureuse o comando DROP PROCEDURE como a seguir:DROP PROCEDURE Nome_Procedure;

Somente o SYSDBA ou o proprietrio da procedure podem apaga-la. Use o comandoALTER PROCEDURE para alterar uma stored procedure. ALTER PROCEDURE temexatamente a mesma sintaxe do comando CREATE PROCEDURE, apenas trocando apalavra CREATE por ALTER. A primeira vista pode parecer que voc no precise docomando ALTER PROCEDURE j que voc pode deletar a stored proceduree depois cria-lanovamente com as alteraes necessrias. Entretanto, isto no ir funcionar se a procedureque voc est tentando alterar chamada por outra stored procedure. Se a stored procedure

1 chama a stored procedure2 voc no pode apagar a stored procedure2 porque a storedprocedure1 depende de sua existncia.

Se voc usar o IBConsole para exibir o metadata de seu banco de dados e examinar ocdigo que cria a stored procedure voc ver que o Firebird primeiro cria todas asprocedurescom o corpo vazio como mostrado no exemplo abaixo:

CREATE PROCEDURE ADD_EMP_PROJ (

EMP_NO SMALLINT,

PROJ_ID CHAR(5)

)

AS

BEGIN EXIT;END ^

CREATE PROCEDURE ALL_LANGS

RETURNS (

CODE VARCHAR(5),

GRADE VARCHAR(5),

COUNTRY VARCHAR(15),

LANG VARCHAR(15)

)

AS

BEGIN

EXIT;

END ^


11/19

Depois de todas as procedures terem sido criadas o script criado pelo Firebird usa ocomando ALTER PROCEDURE para adicionar o corpo de cada stored procedure. Porexemplo:ALTER PROCEDURE ADD_EMP_PROJ(

EMP_NO SMALLINT,

PROJ_ID CHAR(5)

)

AS

BEGIN

BEGININSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);

WHEN SQLCODE -530 DO

EXCEPTION unknown_emp_id;

END

SUSPEND;

END ^

Fazendo isto o Firebird elimina qualquer dependncia entre as proceduresquando elas estosendo criadas. J que o corpo de toda procedureesta vazio no pode haver dependncia deprocedureschamando outra(s). Quando os comandos ALTER PROCEDURE so executadoseles podem ser rodados em qualquer ordem porque a declarao de qualquer procedureque

eventualmente seja chamada pela queest sendo alterada no momento, j existir.

Chamando StoredProcedures

Voc pode chamar stored proceduresde outras stored proceduresou triggers, do IB Consoleou de suas aplicaes. Stored procedures no Firebird so divididas em dois grupos deacordo com como so chamadas. Procedures que retornam valores atravs de parmetrosde sada so chamadas de select procedures porque elas podem ser usadas no lugar deum nome de tabela em um comando SELECT.

Chamando Select Procedures

Select procedures atribuem valores a parmetros de sada e ento executam umSUSPEND para retornar este valores. Abaixo, um exemplo simples de select procedure.CREATE PROCEDURE CUSTOMER_COUNT

RETURNS (


)

AS

BEGIN


SUSPEND;

END ^

A figura 1 abaixo mostra esta proceduresendo chamada a partir do IB Console usando umcomando SELECT. Observe que uma linha e uma coluna foram retornadas e o nome dacoluna o nome do parmetro de sada.


12/19

Figure 1 - Chamando uma select procedurea partir do IB Console

Voc tambm pode chamar proceduresque necessitem de parmetros de entrada a partir doIB Console. A figura 2 mostra um exemplo de chamada da seguinte procedure:

ALTER PROCEDURE ORDER_LIST(

CUST_NO INTEGER

)

RETURNS (

PO_NUMBER CHAR(8)

)

AS

BEGIN

FOR SELECT PO_NUMBER FROM SALES WHERE CUST_NO=:CUST_NO INTO :PO_NUMBER

DO SUSPEND;

END ^


13/19


14/19

Figura 3 A aplicao exemplo que executa a select procedure

O cdigo a seguir foi retirado do evento onclik do boto Execute do formulrio acima emostra como os valores so atribudos aos parmetros de entrada antes da execuo dastored procedure.procedure TProcForm.SelectBtnClick(Sender: TObject);

begin

with ProcDm.OrderListQry do

begin

Params.ParamByName('CUST_NO').Value := CustomerNoEdit.Text;

Params.ParamByName('PO_NUMBER').Value := OrderNoEdit.Text;

Open;

end; //withend;

Chamando uma Non-Select Procedure

A stored procedure a seguir um exemplo de uma non-select procedure , que umaprocedureque no retorna qualquer resultado. Esta proceduretem apenas um parmetro deentrada, FACTOR, e ajusta os salrios mnimo e mximo na tabela JOB por este fator.CREATE PROCEDURE ADJUST_SALARY_RANGE(

FACTOR FLOAT

)

AS

BEGIN

UPDATE JOB

SET MIN_SALARY=MIN_SALARY * :FACTOR, MAX_SALARY=MAX_SALARY * :FACTOR;

END ^

Use o comando EXECUTE PROCEDURE para rodar esta stored procedure a partir de umatrigger, outra stored procedureou do IB Console. Por exemplo:EXECUTE PROCEDURE ADJUST_SALARY_RANGE(1.1);


15/19

Para executar essa stored procedure a partir de sua aplicao use um componenteIBStoredProc e o seguinte cdigo:with ProcDm.AdjustSalRngProc do

begin

Params.ParamByName('Factor').Value := StrToFloat(FactorEdit.Text);

Prepare;

ExecProc;

end; //with

Em tempo de projeto sete a propriedade Database do componente IBStoredProc para ocomponente IBDatabase referente ao BD que contm a stored procedure. Sete apropriedade StoredProcName para o nome da stored procedure que voc quer executar.Use o Property editor da propriedade Params para criar qualquer parmetro de entradanecessrio e configure seus tipos e valores default.

Triggers

Triggersso iguais a stored procedurescom as seguintes excees:

1. Triggers so chamadas automaticamente quando os dados da tabela a qual ela esta

conectada so alterados2.Triggersno tem parmetros de entrada.

3.Triggersno retornam valores.

4.Triggersso criadas pelo comando CREATE TRIGGER.

Usando CREATE TRIGGER

O comando CREATE TRIGGER, a seguir, mostra todos os elementos da sintaxe docomando. As palavras-chave CREATE TRIGGER so seguidas do nome da trigger, a seguira palavra-chave FOR e ento o nome da tabela a qual a trigger estar relacionada. Emseguida vem a palavra ACTIVE (ativa) ou INACTIVE (inativa) indicando se a triggerdever

ou no ser executada. Se a trigger est inativa ela no ser executada. Voc ver comoativar e desativar uma trigger mais tarde neste artigo. O prximo elemento do comandoCREATE TRIGGER indica quando a triggerser executada.

1. BEFORE UPDATE (Antes de uma atualizao)2. AFTER UPDATE (Aps uma atualizao)3. BEFORE INSERT (Antes de uma incluso)4. AFTER INSERT (Aps uma incluso)5. BEFORE DELETE (Antes de uma excluso)6. AFTER DELETE (Aps uma excluso)

A seguir vem a palavra chave opcional POSITION seguida de um nmero inteiro. O Firebird

permite que voc conecte quantas trigger quiser ao mesmo evento. Por exemplo, vocpoderia ter quatro triggersligadas a tabela EMPLOYEE todas como AFTER UPDATE. Esta uma grande caracterstica j que permite que voc modularize seu cdigo. Entretanto aordem em que as triggervo ser executadas pode eventualmente ser importante. A palavrachave POSITION te d o controle da ordem de execuo baseado no inteiro informado. Noexemplo abaixo a triggermostrada ser executada primeiro porque a sua posio 0 (zero).Se existissem trs ou mais triggersvoc poderia atribuir as suas posies os valores 10, 20 e30. uma boa ideia deixar um espao entre a numerao para que voc possa facilmenteinserir outras triggerscom pontos de execuo entre as j criadas.


16/19

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE

ACTIVE

AFTER UPDATE

POSITION 0

AS

BEGIN

IF (old.salary new.salary) THEN

INSERT INTO salary_history

(emp_no, change_date, updater_id, old_salary, percent_change)

VALUES (

old.emp_no,

'NOW',

user,

old.salary,

(new.salary - old.salary) * 100 / old.salary);

END^

Aps a palavra chave AS vem a declarao de qualquer varivel local usando o comandoDECLARE VARIABLE igual ao que foi usado para stored procedures. Finalmente vem ocorpo da proceduredelimitado pelos comandos BEGIN-END.

Uma coisa para se ter em mente quando estiver usando triggers que uma simplesalterao em um banco de dados pode causar o disparo de vrias triggers. Uma alterao na

tabela A pode disparar uma triggerque atualiza a tabela B. A atualizao da tabela B, porsua vez, pode disparar uma trigger que insere um novo registro na tabela C o que podeprovocar o disparo de uma triggerque atualiza a tabela D e assim sucessivamente.

O segundo ponto importante sobre triggers que uma trigger parte da transao que adisparou. Isto significa que se voc inicia uma transao e atualiza uma linha que disparauma triggere esta triggeratualiza outra tabela que dispara outra triggerque atualiza outratabela e voc ento d um ROLLBACK na transao, tanto sua alterao quanto todas asalteraes que foram feitas pela srie de disparos de triggers, sero canceladas.

BEFORE ou AFTER?

Uma triggertem que ser disparada antes do registro ser atualizado caso voc queira alterar ovalor de uma ou mais colunas antes que a linha seja atualizada ou caso voc queira bloqueara alterao da linha gerando uma EXCEPTION. Por exemplo, voc teria de usar uma triggerBEFORE DELETE para evitar que o usurio deletasse o registro de um cliente que tenhacomprado nos ltimos dois anos..

Triggers do tipo AFTER so usadas quando voc quer garantir que a atualizao quedisparou a trigger esteja completa com sucesso antes de voc executar outras aes. Atrigger acima um bom exemplo. Esta trigger insere uma linha na tabela salary_historysempre que o salrio de um funcionrio alterado. A linha de histrico contm o salrioantigo e o percentual de alterao. Como a atualizao do registro do funcionrio pode falharpor vrias razes, como um valor em um campo que viola restries impostas por exemplo,

voc no vai querer criar o registro de histrico at que a atualizao seja completa comsucesso.

Usando OLD e NEW

No exemplo de trigger acima voc pode ver nomes de campos precedidos das palavrasOLD e NEW. No corpo de uma trigger o Firebird deixa disponveis tanto o valor antigocomo o novo valor de qualquer coluna, por exemplo old.salary e new.salary. Usando osvalores OLD e NEW voc pode facilmente criar registros de histrico, calcular o percentualde alterao de um valor numrico, encontrar em outras tabelas registros que combinem com


17/19

o valor antigo ou novo de um campo ou fazer qualquer outra coisa que voc precise fazer.

Gerando EXCEPTIONS

Em uma triggerdo tipo BEFORE voc pode evitar que a alterao que disparou a triggersejaefetivada, gerando uma EXCEPTION. Antes que voc possa gerar uma EXECPTION vocprecisa cria-la usando o comando CREATE EXCEPTION. Por exemplo:CREATE EXCEPTION CUSTOMER_STILL_CURRENT

Este cliente comprou nos ltimos dois anos.

Onde as palavras-chave CREATE EXCEPTION so seguidas do nome da exceo e dotexto da mensagem de erro para esta exceo. Para gerar esta EXCEPTION em uma triggerou storedprocedureuse a palavra chave EXCEPTION como mostrado abaixo:EXCEPTION CUSTOMER_STILL_CURRENT;

Quando voc gera uma EXCEPTION a execuo da trigger ou da stored procedure terminada. Qualquer comando na triggerou stored proceduredepois da exceo no serexecutado. No caso de uma triggerdo tipo BEFORE a atualizao que disparou a triggerabortada. Finalmente a mensagem de erro da exceo retornada para a aplicao. Vocpode deletar uma EXCEPTION usando o comando DROP EXCEPTION e alterar a

mensagem associada usando o comando ALTER EXECEPTION. Por exemplo:ALTER EXCEPTION CUSTOMER_STILL_CURRENT Este cliente ainda est ativo.;

DROP EXCEPTION CUSTOMER_STILL_CURRENT;

Usando GENERATORS

O Firebird no tem um tipo de campo autoincrementvel. Ao invs disso tem uma ferramentamais flexvel chamada GENERATOR. Um GENERATOR retorna um valor incrementado todavez que voc o chama. Para criar um GENERATOR use o comando CREATE GENERATORcomo a seguir.CREATE GENERATOR CUSTOMER_ID;

Para excluir um GENERATOR basta usar o comando DROP GENERATOR, note que essecomando s vlido para o Firebird j que no Interbase isso no possvel, pelo ,menos ata sua verso 6. Para obter o prximo valor de um GENERATOR use a funo GEN_ID() porexemplo:GEN_ID(CUSTOMER_ID, 1);

O primeiro parmetro o nome do GENERATOR e o segundo o incremento. No exemplo ovalor retornado ser o ltimo valor mais um. O Incremento pode ser qualquer valor inclusivezero, o que muito til para se obter o valor corrente de um GENERATOR sem alterar seuvalor. Voc pode tambm alterar o valor de um GENERATOR a qualquer momento usando ocomando SET GENERATOR com a seguir:SET GENERATOR CUSTOMER_ID TO 1000;

Note que se voc chamar GEN_ID dentro de uma transao e ento executar umROLLBACK o valor do GENERATOR no retornar ao valor anterior. Ele no influenciadopelo ROLLBACK. GENERATORS so frequentemente usados em triggerspara fornecer umvalor para uma chave primria como no exemplo a seguir:


18/19

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

new.emp_no=gen_id(emp_no_gen, 1);

END ^

Voc pode tambm chamar GEN_ID em uma stored procedure que retorna o valor doGENERATOR em um parmetro de sada para a aplicao cliente. O cliente pode entoatribuir o valor a sua chave primria e apresenta-la ao usurio quando este cria um novoregistro antes mesmo que este tenha sido gravado com um POST. Neste caso voc poderquerer tambm criar uma triggercomo a mostrada acima para o caso de um cliente inserirum registro e no fornecer um valor para a chave primria. Tudo que voc tem de fazer alterar o cdigo como a seguir:IF new.emp_no IS NULL THEN new.emp_no = gen_id(emp_no_gen, 1);

Agora a triggers ira fornecer um valor para a chave primria no caso do campo ser nulo.

Alterando e excluindo Triggers

Voc pode tambm usar o comando ALTER TRIGGER para alterar tanto o cabealho quanto

o corpo da trigger. O uso mais comum da alterao do cabealho ativar e desativar umatrigger. Outro uso trocar o POSITION da trigger. O comando a seguir ir inativar a trigger.

ALTER TRIGGER SET_EMP_NO INACTIVE;

Para alterar apenas o corpo da trigger fornea seu nome sem as outras informaes docabealho e ento o novo corpo como mostrado a seguir. Voc pode tambm alterar ocabealho e o corpo ao mesmo tempo.ALTER TRIGGER SET_EMP_NO

AS

BEGIN

IF new.emp_no IS NULL THEN new.emp_no = gen_id(emp_no_gen, 1);

END ^

Para apagar uma triggeruse o comando DROP TRIGGER. Por exemplo:DROP TRIGGER SET_EMP_NO;

Sumrio

Stored procedures e triggers so o corao do desenvolvimento de aplicaescliente/servidor. Usando stored procedurese triggersvoc pode:

1. Reduzir o trfico de rede.

2. Criar um conjunto comum de regras de negcio no banco de dados que se aplicar atodas as aplicaes cliente.

3. Fornecer rotinas comuns que estaro disponveis para todas as aplicaes clientereduzindo assim o tempo de desenvolvimento e manuteno.

4. Centralizar o processamento no servidor e reduzir os requisitos de hardware nasestaes cliente.

5. Aumentar a performance das aplicaes.


19/19

Para mais informaes sobre o uso de stored procedurese triggersconsulte Data DefinitionGuide e Language Guide como tambm as stored procedures e triggers no banco dedados de exemplo EMPLOYEE.GDB.

Artigo Original:

Bill Todd

Borland Developers Conference San Diego 2000

Traduo e adaptao:

Alessandro Fernandes

[email protected]

Comunidad e Firebird de Lngua Portuguesa

Visite a Comunidade em:

http://www.comunidade-firebird.org

A Comunidade Firebird de Lngua Portuguesa foi autorizada pelo Autor do Original para elaborar esta traduo.
mailto:[email protected]://www.comunidade-firebird.org/mailto:[email protected]

Documents

stored procedures e triggers no firebird