Saturday, March 18, 2006

Small Firebird trigger example

After reading a post in the Firebird forum at Devshed I immediately put up a small example about writing a trigger (and also upgraded my favourite tool, Flamerobin, to the latest version) , here it goes:

1. Create an empty table












2. Create the trigger on it using Flamerobin's guided menu




As you can see the menu will guide you through defining the trigger name, to activate it (in other words you can deactivate triggers without deleting them), setting it to fire before or after a DML statement (insert/update/delete) and also define it's position, which means that many triggers with the same action (i.e. "before insert") can exist on the same table and the position will tell the firing order.
Now the trigger code:

SET TERM ^ ;

CREATE TRIGGER modified_when FOR TEST_TABLE
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
/* enter trigger code here */
NEW.updated_at = CURRENT_TIMESTAMP;

END^

SET TERM ; ^


Note that after every DML and DDL statement you need to commit to make changes effective.
Now the test phase, I'll insert a dummy row:

insert into test_table (name_field) values ('pippo');

And check for the insert result completed by the trigger action:


See the result? ;-)

More infos about triggers in Firebird can be found in this article.

BTW: isn't Flamerobin's code autocompletion great?

No comments: