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:

|

| -->