Notação:
Na maior parte deste documento, nos trechos de programa e no texto, palavras reservadas da linguagem SQL (ou do SQL procedural do Interbase) estão denotadas em negrito e itálico e variáveis definidas pelo usuário com tipo normal ou em negrito apenas como em:
        select emp_no from employee;
Palavras reservadas e nomes de objetos da BD como nomes de tabelas, colunas, procedimentos armazenados, triggers, etc não são sensíveis a maiúsculas e minúsculas no SQL do  Interbase (com exceção de literais entre aspas simples, por exemplo: 'NOME_TABELA').

Exemplo 1:

CREATE PROCEDURE ADD_MULT_NUMBERS (num1 int, num2 int) returns (mysum int, prod int) AS
                         /* add and multiply two given numbers as input and returns their sum and their product  as output*/
declare
variable sum1  int;
begin
   sum1 = num1 + num2;
   mysum=  sum1;
   prod= num1 * num2;
   suspend;
 end;

Variáveis locais:
 Podem ter os mesmos tipos da linguagem SQL e ser usadas em comandos de atribuição e em expressões.

Comandos para  controle de fluxo:

if  (condicao) then
  <bloco de comandos>|<comando>
else
<bloco de comandos>|<comando>

onde <bloco de comandos> = begin comando [comando]... end

while (condicao)  do

<bloco de comandos>|<comando>
 

Para testar um procedimento armazenado  sob isql V. pode executar um dos dois comandos abaixo:

• execute procedure nome_procedimento(lista-de-parametros);
• select  <lista de parâmetros de saída | * >  from nome_procedimento(lista-de-parâmetros-de-entrada) where ... etc;

onde  o nome do procedimento é usado como se fosse uma tabela. Os nomes dos parâmetros de saída são usados como se fossem nomes de colunas.Veja os exemplos a seguir para chamar o procedimento ADD_MULT_NUMBERS definido anteriormente.

A semântica da execução dos dois comandos acima não é a mesma, porém: execute procedure retorna no máximo uma linha enquanto select from nome_procedimentopode retornar múltiplas linhas e é mais flexível pois permite utilizar o procedimento armazenado num comando select como se fosse uma tabela (com parâmetros). O comando suspend serve justamente para sincronizar o comando invocador select nome_procedimento com o procedimento chamado: quando a linha retornada é consumida pelo chamador o procedimento chamado retoma a execução e assim sucessivamente. O exemplo abaixo ilustra esse ponto:

Exemplo 2:
create procedure P(n int) returns (r int) as
begin
    r=0;
    while ( r < n) do
    begin
        r= r+1;
        suspend;
    end
end;

O comando isql:  select r from P(3); mostrará as linhas:
        1
        2
        3
enquanto o comando select r from P(-1) não mostrará nada pois o procedimento P termina antes de se sincronizar com o chamador ( o mesmo efeito poderia ser obtido se no corpo de P fosse executado o comando exit; ).
Se agora executarmos o comando isql: execute procedure P(3); apenas uma linha contendo o valor 1 é retornada, o que provavelmente não é o efeito desejado!

Agora vejamos um exemplo mais elaborado, contido no esquema da BD exemplo employee.gdb do Interbase: o procedimento armazenado get_emp_proj toma como parâmetro de entrada o número de um funcionário (emp_no) e retorna os nomes dos  projetos  (proj_id) em que esse funcionário trabalha (possivelmente vários) e que estão na tabela employee_project:

Exemplo 3:
create procedure get_emp_proj (emp_no smallint) returns (proj_id char(5)) as
begin
    for select proj_id
    from employee_project
    where emp_no = :emp_no
    into :proj_id
    do
        suspend;
end;

Vários pontos precisam ser esclarecidos sobre esse exemplo:

• O comando for select  col1, col2, coln ... pode retornar multiplas linhas:  para cada linha retornada  os valores das colunas selecionadas devem ser atribuídos a variáveis locais do procedimento (com tipos equivalentes) através da  cláusula into :var1, :var2.. :varn (e que deve ser a última cláusula do comando for select);  o bloco de comandos após do é executado para cada linha retornada pelo comando for select. Neste exemplo a linha é retornada pelo procedimento para o comando select invocador do procedimento (via comando suspend ).
• O comando for select tem o mesmo efeito da criação um cursor implícito para o comando select e que processa cada linha do resultado do select no bloco do (veja uma introdução a cursores mais adiante).
• Dentro de comandos SQL incluídos  no  corpo de um procedimento, referências a variáveis locais ou parâmetros formais do procedimento devem ser precedidas por ":". Por esta razão não há ambiguidade na cláusula where do comando for select acima, entre a coluna emp_no da tabela employee_project e a variável local de mesmo nome  e referenciada por :emp_no; idem para a coluna proj_id e o parâmetro formal proj_id.
• procedimentos armazenados contendo o comando for select são normalmente invocados via select nome_procedimento ( ), e por esta razão são denominados de select procedures nos manuais do Interbase, enquanto que os chamados via execute  retornam apenas um valor e são denominados  execute procedures. Em um procedimento chamado via execute o comando suspend funciona como se fosse exit.

Se agora executarmos o comando isql:

select * from get_emp_proj(8);

3 linhas são retornadas:
        VBASE
        GUIDE
        MKTPR
correspondendo aos três projetos em que o funcionário 8 trabalha.

Cursores:

Um cursor SQL é semelhante a um file pointer para um arquivo  Unix: se considerarmos as linhas da  saída de um comando select como se fossem as linhas de um arquivo ao qual associamos um cursor, podemos ler sequencialmente via cursor cada uma dessa linhas.  As principais operações com um cursor SQL são:

• declarar um cursor associando-o a uma consulta  select
• abrir o cursor (via open nome_cursor) o que essencialmente executa o comando select
• obter a próxima linha da consulta em variáveis locais do bloco  ( via fetch into ... )
• testar o cursor para o caso de não haver mais linhas a ler.
• fechar o cursor (close nome_cursor)

Exemplo 4:            /* send greetings to high paid employees;  send thanks to the President for salary raise!*/

#include <stdio.h>
exec sql begin declare section;
 char fname[10];
  char fjob[9];
exec sql end declare section;
main()
{
exec sql  declare cursor mycursor for
     select ename, job
     from emp
     where sal > 2000;
exec sql  open mycursor;
 while ( !SQLCODE){
      exec sql  fetch mycursor into :fname, :fjob;
      if (strcmp(fjob, 'PRESIDENT)
         printf ("%s\n", 'Good morning Mr President!, thanks for the salary raise, Mr. fname!');
   else
      printf ("%s\n",'Good morning  fname I think you are a great  fjob');
 }
  exec sql close mycursor;
)

Exceções

Se um procedimento armazenado ou trigger detecta uma situação de erro, ele  pode "levantar uma exceção" previamente definida. Uma exceção no Interbase, é um objeto armazenado no SGDB  contendo uma mensagem que é enviada para a  aplicação quando a exceção é "levantada". Um exemplo de criação de  exceção:

     create exception sal_greater_manager 'Employee salary cannot be greater than his managers salary'

Uma exceção é levantada no corpo de um procedimento armazenado ou trigger através do comando:

        exception nome_da_exceção;

No exemplo 5, a seguir, veremos uma utilização simples desses dois comandos.
Quando este comando é executado o SGBD  desfaz todas as ações executadas pelo procedimento ou trigger (diretas ou indiretas) termina o procedimento ou trigger e retorna para a aplicação a mensagem de erro contida na  criação da exceção .
Observação:  caso o procedimento remoto ou trigger contenha um comando for select,  apenas as ações feitas após o último comando suspend são desfeitas.

Opcionalmente a exceção pode ser "tratada" pelo procedimento armazenado ou trigger, caso o  comando when nome-exceção  do <bloco de comandos>  seja  incluído no final do corpo do procedimento ou trigger.  Nesse caso o bloco de comandos é executado  (a mensagem contida na exceção não é retornada mas outras mensagens de erro podem ser geradas no bloco de comandos, inclusive  executar ações corretivas na BD) e o procedimento/trigger termina. De qualquer forma a transação corrente é abortada.
Várias exceções distintas podem ser levantadas e (opcionalmente) tratadas no corpo de um procedimento.
Opcionalmente também, condições de erro do sistema de execução do SQL, registradas na variável global SQLCODE, ou condições de erro do SGBD, registradas na variável global GDSCODE, podem também ser detectadas e tratadas pelo procedimento remoto ou trigger.
Para este fim o comando when deve especificar, por exemplo:

    when SQLCODE -803 do
        begin
                    /* comandos   para tratar o erro -803, de inserção/atualização violando a unicidade da Chave Primária */
        end

when GDSCODE  coderro do
         begin
                    /* comandos   para tratar o erro  coderro do SGDB            */
        end
 

Triggers:

Tipos de Triggers:

Apenas triggers disparados por comandos DML (insert, update, delete) sobre tabelas ou visões podem ser criados no Interbase.
Outros SGBDs como por exemplo Oracle, suportam triggers disparados por comandos DDL (create, alter, drop: Schema triggers) e triggers disparados por operações globais na  BD (logon, logoff, startup, shutdown, etc: Database triggers).
 

Notação:
triggering statement, triggering action ou triggering event: é o comando do cliente (aplicação) que disparou um trigger específico.

Outra classificação incluída no padrão SQL3:

• row trigger: trigger definido com a opção for each row : êle é disparado a cada linha atualizada pelo  triggering statement (Obs: um comando SQL update/insert/delete pode atualizar várias linhas de uma tabela)
• statement trigger: quando a  opção for each row não é usada (disparado apenas uma vez por um triggering statement)

 

No Interbase um trigger pode ser associado únicamente a uma das operações insert, update, delete, e não a qualquer combinação dessas operações como no padrão SQL3 ( e também no Oracle). Se ações do trigger forem requeridas para cada uma dessas operações então 3 triggers distintos devem ser criados. Por outro lado, mais de um trigger para uma mesma operação pode ser criado, e nesse caso, uma prioridade pode ser especificada para a ordem de execução.

Opções before e after:

Estas opções definem o instante no tempo em que as ações contidas no corpo do trigger são executadas: se antes da execução do triggering statement, ou após a execução do triggering statement. Elas têm implicações muito importantes na semântica da execução de um trigger, como veremos no exemplo 6 a seguir.. Como na verdade os triggers Interbase são row triggers, se a opção before for escohida, o trigger é disparado antes de cada atualização feita pelo triggering statement. Se a opção after for escohida, o trigger é disparado depois  de cada atualização feita pelo triggering statement.
É possivel criar dois triggers distintos, um com a opção before e outro com a opção after.

Alias old e new :
Como no padrão SQL3, quando linhas são atualizadas por um  triggering statement , o alias old permite acessar as linhas antes da atualização da tabela e o alias new permite acessar as linhas com os valores atualizados; old e new podem ser interpretados como alias para duas tabelas temporárias contendo as linhas citadas e só acessíveis durante a execução do trigger. Como Interbase só implementa  row triggers apenas a linha correntemente sendo atualizada está disponível durante a execução do trigger (o mesmo ocorre com Oracle).
 

Sintaxe do comando Create Trigger:
É praticamente idêntica à de um procedimento armazenado com a diferença de que um trigger não pode ter parâmetros de entrada ou de saída e adicionalmente deve especificar:

• o nome da tabela ao qual está associado,
• uma das operações de atualização feitas pela aplicação e que disparará o trigger (insert, update, delete),
• uma das opções before ouafter,
• opcionalmente se o trigger deve ser criado ativo (default) ou inativo,
• opcionalmente uma prioridade para triggers do mesmo tipo (before ou after) e sobre a mesma operação (extensão Interbase, através da cláusula position numero, onde um número menor significa prioridade maior)

• no caso da operação update, uma ou mais colunas, e que somente se elas forem atualizadas  o trigger será disparado (por exemplo, a coluna salário da tabela  Funcionários)
• uma cláusula when (condição) que somente se  avaliada  true implica na execução do corpo do trigger.

Exemplo 5:
Este trigger  impõe a seguinte "regra de negócio: nenhum funcionário pode ter seu salário diminuído devido a uma atualização errônea". Ele  é disparado quando a tabela employee (da Base de Dados exemplo employee.gdb ) é atualizada,  verifica se a coluna salário foi atualizada e caso o novo salário seja inferior ao antigo, repõe o salário antigo, defazendo assim a atualização feita pelo triggering statement (o trigger poderia também levantar uma exceção e abortar a transação, mas a implementação a seguir é mais eficiente pois muitas atualizações legais poderiam ser feitas no mesmo comando SQL;  é simples estender o trigger para registrar numa tabela de log as atualizações ilegais desfeitas!)
 

create trigger check_salary for employee              /* no employee may have a salary decrease  */
active before update position 0 as
begin
   if (old.salary > new.salary) then
     new.salary = old.salary;
end;

• Na 1ª linha colocamos o nome do trigger e o da tabela sobre o qual o mesmo irá atuar,
• na 2ª  linha especificamos que o trigger deverá ser disparado antes da execuçao da atualização na tabela employee,  que a operação associada é update e que a prioridade do trigger é máxima (0).
• na 4ª  linha a coluna old.salary contém o valor do salário antes da atualização e a coluna new.salary contém o novo valor do salário (mesmo que esse valor ainda não tenha sido colocado na tabela original!)
• na 5ª linha acontece a mágica: passamos para new.salary o valor do salário antigo e é esse valor que o statement trigger vai usar na atualização. Em outras palavras, o novo valor da coluna é disponibilizado em new.salary e caso ele seja mudado pelo trigger e  como escolhemos para o trigger a opção before, é esse valor que será inserido na tabela. O efeito seria nulo caso o trigger tivese  sido criado com a opção after!

Exemplo 6:
Este exemplo implementa a aparentemente  simples regra de negócio:
"nenhum funcionário pode ter o seu salário maior do que o salário do seu chefe".

Este trigger será associado a updates na tabela employee. Nesta tabela a coluna dept_no contém o número do departamento onde o funcionário está lotado. A tabela  department contém para cada dept_no (que é a Chave Primária), na coluna mngr_no  o número do funcionário que é o gerente (manager) do departamento.  Via junções apropriadas  o trigger  usa o número do departamento do funcionário  atualizado (new.dept_no) para obter o número do gerente na tabela department e com esse número obtém o seu salário em m.salary copiando-o na variável local m_salary. No bloco do este salário é comparado com o novo salário  new.salary e, se for menor, a exceção sal_greater_manageré levantada e a transação é abortada pelo SGBD, ou seja, o salário não é atualizado.
Obs: o código do trigger poderia ser muito mais simples  colocando no comando if, à direita do sinal > uma subconsulta idêntica à consulta do comando for select (sem a cláusula into). Infelizmente o SQL do  Interbase não permite isto.

create exception sal_greater_manager  'Employee salary cannot be greater than his managers salary';

create trigger check_manager for employee        /* No employee may have a salary greater than his manager's salary */
active before update position 5 as
declare variable m_salary numeric (12,2);
declare variable d_mngr int;
begin
        for select m.salary, d_mngr_no
       from employee m, department d
        where new.dept_no = d.dept_no
        and m.emp_no = d.mngr_no
       into :m_salary,  :d_mngr_no
       do
        begin
        if ( new.salary > m_salary and new.emp_no <>d_mngr_no )  then
       exception sal_greater_manager;
        end
end;

Observação final:
este trigger permite desfazer uma (ou mais de uma) atualização  na tabela employee feita(s) pelo triggering statement. Na terminologia da Oracle esta tabela é denominada de tabela mutante, e não pode ser atualizada ou sequer consultada pelo código do trigger. A proposta da Oracle para resolver o problema envolve a codificação de dois triggers associados à operação update: um before row trigger que guarda as atualizações numa tabela temporária e  um after statement trigger  que lê a tabela temporária e desfaz as atualizações ilegais. Obviamente, ela é muito mais complexa do que a solução acima!
Para ser completa a solução, deveríamos escrever um outro trigger idêntico ao acima para a operação de inserção (insert) na tabela employee. Segundo o padrão SQL3, poderia ser o mesmo trigger simplesmene acrescentando na 2ª linha após update: , insert .

Comando alter: modificando procedimentos armazenados e triggers

Para modificar um procedimento armazenado no diocionário do SGDB utilize o comando alter com a mesma sintaxe do comando create procedure:
        alter procedure  nome_do_procedimento (lista de parâmetros de entrada) returns (lista de parametros_saida) as ..etc
Para modificar um trigger  a sintaxe é:
alter trigger nome_do_trigger   cabeçalho _do_trigger   corpo_do _trigger
Podem ser feitas modificações parciais na definição do cabeçãlho de um trigger, o que é muito útil para testes, por exemplo: tornar um trigger temporariamente inativo ou alterar a operação (insert/delete/update) do trigger ou sua prioridade.
Exemplo:
       alter trigger chek_salary inactive

Obtendo informações sobre procedimentos armazenados e triggers

As tabelas rdb$procedures e rdb$triggers do dicionário do Interbase podem ser consultadas para obter dados de procedimentos armazenados e triggers, respectivamente. Os exemplos abaixo contêm algumas consultas úteis, onde os nomes das colunas selecionadas são auto-explicativos:

       select rdb$procedure_source
        from rdb$procedures where rdb$procedure_name like 'ADD_MULT_NUMBERS%'

      select rdb$relation_name, rdb$trigger_type, rdb$trigger_inactive, rdb$trigger_source
        from rdb$triggers where rdb$trigger_name like 'CHECK_SALARY%'

Obs: (i) embora a linguagem SQL do Interbase  seja insensível a maiusculas e minúsculas, há uma exceção: literais entre aspas '..' são comparados exatamente como escritos. Como nomes de objetos quaisquer são armazenados em maiúsculas no dicionário do Interbase, devemos colocá-los também em maiúsculas quando dentro de literais.
(ii) Detalhes dos esquemas das tabelas contidas no Dicionário do Interbase podem ser vistos no capítulo 7, "System Tables and Views", do manual  Language Reference do Interbase.

Problema desafio:
O trigger do exemplo 6 acima  não leva em consideração que existe uma hierarquia entre os departamentos da BD employee.gdb: as seguintes colunas da tabela departments são relevantes para o problema em questão: para cada departamento (dept_no),  chefiado pelo funcionário mngr_no, existe um departamento hierarquicamente superior (head_dept), cujo gerente (mngr_no) é, em princípio,  chefe do gerente do departamento hierarquicamente abaixo. Portanto, o trigger deveria verificar, caso o aumento de salário seja para um gerente, se este aumento não conflita com o salário do gerente hierarquicamente superior.

Existem três complicadores adicionais:

1. existe um departamento mais alto na hierarquia cujo head_dept é inexistente e tem o valor NULL;
2. adicionalmente a semântica da hierarquia de departamentos poderia permitir a acumulação de cargos de gerência, isto é, um funcionário ser gerente de mais de um departamento, o que pode implicar em percorrer a hierarquia por mais de um nível;
3. o terceiro complicador é decorrente de uma omissão infeliz na implementação do SQLdo Interbase: quando se utiliza a cláusula into em um comando for select ou no comando fetch ... into de um cursor, a linguagem SQL permite especificar uma variável indicadora adicional, para sinalizar se o valor lido da tabela tem o valor NULL ou não (seria inapropriado fazê-lo com um valor especial da própria variável local, digamos 0 para um valor numérico ou vazio ('') para uma cadeia, porque esses valores poderiam ser valores válidos e semanticamente distintos de NULL). A variável indicadora permite eliminar esta ambiguidade: ela toma o valor 0 se o valor da variável associada é "não nulo"  e -1 caso contrário. A sintaxe usada pelo SQL é : into nome_variavel indicator nome_variavel_indicadora. Infelizmente o comando for select do Interbase não suporta variáveis indicadoras na cláusula into (nem tãopouco coloca um valor testável na variável local quando o valor originário da tabela é Nulo!), embora aparentemente o faça com cursores e fetch .. into, mas que, por outro lado,  não podem ser usados em triggers ou procedimentos armazenados.