Never been to TextSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

firebird auto increment trigger (See related posts)

This is a trigger to implement auto-increment/identity columns in Firebird. Another version is using a stored procedure to
get the value of the generator before inserting it then returning the saved generator value. This is needed because Firebird
generators are outside transaction control so reading the generator value again after using it may yield a different number
because it has already been used in a different transaction.

CREATE GENERATOR testgen;

SET TERM $$ ;
CREATE TRIGGER trg_gen_test FOR test_table
ACTIVE
BEFORE INSERT AS
BEGIN
  new.identity_column = gen_id(testgen, 1);
END $$
SET TERM ; $$


SET TERM $$ ;

CREATE PROCEDURE proc_insert ( val1 VARCHAR(20), val2 VARCHAR(20) ) RETURNS 
(generated_id INTEGER)
AS
BEGIN
  generated_id = gen_id(testgen, 1);
  INSERT INTO test_table (identity_column, col1, col2) VALUES (:generated_id, :val1, :val2);
  SUSPEND;
END $$

SET TERM ; $$

/* call the above procedure like this from code (PHP):
SELECT generated_id FROM proc_insert('a', 'b');
of course you can prepare and use placeholders/bind params for the proc parameters
*/


You need to create an account or log in to post comments to this site.


Related Posts