19:
  20:/*
  21: *  Purpose:
  22: *    This script provides the autoinc_gen procedure. It can be used to
  23: *    dynamically generate triggers that set generators.
  24: *
  25: *
  26: *  Note:
  27: *    This script must be executed prior to creating tables.
  28: *
  29: *
  30: *  Usage:
  31: *      First, make this procedure available to the database.
  32: *      Each table that requires a generator uses the following
  33: *      script snippet:
  34: *
  35: *      CREATE TABLE test_autoinc (
  36: *          ASequence Integer,
  37: *          Description Varchar(255)
  38: *          PRIMARY KEY  (ASequence)
  39: *      );
  40: *
  41: *      create sequence ASequence_seq;
  42: *      execute procedure autoinc_gen ('test_autoinc', 'ASequence', 'ASequence_seq');
  43: *      commit;
  44: *
  45: *
  46: *  Warning:
  47: *      Using EXECUTE STATEMENT with DDL can be dangerous if COMMIT
  48: *      is not called immediately after calling the procedure that
  49: *      calls EXECUTE STATEMENT.
  50: *
  51: */
  52:
  53:--Dynamically create trigger that fires a generator on a primary key column
  54:set term ^;
  55:create procedure autoinc_gen (TABLE_NAME varchar(27), COLUMN_NAME varchar(31), SEQUENCE_NAME varchar(31) )
  56:as
  57:declare variable trig_name varchar(31);
  58:declare variable sql varchar(1024);
  59:begin
  60:
  61:trig_name='tib_' || :TABLE_NAME;
  62:
  63:sql = 'create trigger ' || :trig_name || ' FOR ' || :TABLE_NAME ||
  64:    ' BEFORE INSERT POSITION 0 AS ' ||
  65:    ' BEGIN ' ||
  66:    '   IF (( new.' || :COLUMN_NAME || ' is null ) or ( new.' || :COLUMN_NAME || '=0)) then ' ||
  67:    'new.' || :COLUMN_NAME || '= gen_id(' || :SEQUENCE_NAME || ',1); ' ||
  68:    ' END'
  69:    ;
  70:
  71:execute statement sql;
  72:
  73:/*
  74: *  If we wanted to be really clever we could probably dynamically create
  75: *  a trigger description here using the new COMMENT ON syntax of Fb 2.0
  76: *
  77: */
  78:
  79:end^
  80:set term ;^
  81:commit;
  82:
-->