Advertisement
econz

Firebird - Gera PK, Generator e Trigger ao criar tabela

Jul 17th, 2019
210
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.93 KB | None | 0 0
  1. Referencia:
  2. https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-psql-ddltriggers.html
  3.  
  4. CREATE OR ALTER TRIGGER TRG_PADRAO_CRIA_TAB
  5. active after CREATE TABLE POSITION 0
  6. AS
  7. DECLARE variable vs_Tabela VARCHAR(50);
  8. BEGIN
  9.    
  10.    vs_Tabela = rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME');
  11.  
  12.    -- Cria a PK
  13.    EXECUTE statement 'alter table ' || :vs_Tabela  || ' add constraint PK_' || :vs_Tabela || ' primary key (ID)';
  14.  
  15.    -- Cria o generator
  16.    EXECUTE statement 'create sequence GEN_' || :vs_Tabela  || ' start with 0 increment by 1';
  17.  
  18.    -- Cria a trigger que alimenta o generator
  19.    EXECUTE statement
  20. 'create or alter trigger TRG_' || :vs_Tabela || '_BI0 for ' || :vs_Tabela ||
  21. ' active before insert position 0
  22. as
  23. begin
  24.  
  25.   /*
  26.      Sobre...: Novo registro
  27.      Revisao.: 1
  28.   */
  29.  
  30.   if ((new.ID is null) or (new.ID<=0)) then
  31.      new.ID = gen_id(GEN_'|| :vs_Tabela || ',1);
  32.  
  33. end';
  34.  
  35. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement