--- In firebird-support@yahoogroups.com, "heirkeyso" <heirkeyso@i...>
wrote:

>
> greetings!
>
> i'm already a firebird user for 3years but i'm not yet full utilized
> the firebird. using the tables or the data storage, generator,
> administrator feature(like backup,restore and etc) and sql feature of
> the software i was only using. i want to use the trigger and store
> procedure capability but i don't know where to start. please give some
> documentation or book about creating script on firebird, in order to
> utilized the feature of firebird.
>
> thanks.
>

Triggers and Stored Procedures use the PSQL language. The Firebird
Book is a good reference to have, but if you want to get started
straight away, then the IB6 documentation is 99% relevant in this
area. In particular Langref.pdf contains most of the syntax, just
check the release notes for the newer features.

There are 6 types of triggers. Before / After -> Insert / Update /
Delete. Triggers are a row level operation, and can be used for lots
of different purposes. Remember that the triggers will fire even if
you are updating all the fields to the exact same value.

You can inspect the values of NEW.fieldname and OLD.fieldname, with
the exception of insert triggers (which do not have OLD values
obviously), and delete triggers (which do not have NEW values obviously).

A simple example is a trigger to emulate an autoincrement field.

SET TERM ^ ;

CREATE TRIGGER MYTABLE_BI FOR MYTABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(GEN_MYTABLEID, 1);
END
END
^

SET TERM ;
^

COMMIT WORK;

Now whenever you insert a new record into MYTABLE, if you leave ID
null, a value is automatically retrieved from the generator and placed
into the record.

The other thing you will notice is that I have used SET TERM. Triggers
and Stored Procedures contain semi colon (;) characters. iSQL and most
other tools interpret this as an end of command, and because triggers
and stored procedures potentially contain multiple commands which
means you cant really declare a trigger without changing the
terminator character to something else (temporarily).

There are two kinds of stored procedures, executable and selectable.
Selectable stored procedures must return at least 1 field, and can
return multiple records. Executable stored procedures may return 0
fields or a couple of values.

SET TERM ^ ;

CREATE PROCEDURE GETNUMBERSBETWEEN
(
MINVALUE INTEGER,
MAXVALUE INTEGER
)
RETURNS
(
NUM INTEGER
)
AS
BEGIN
NUM = :MINVALUE;
WHILE (:NUM <= :MAXVALUE) DO
BEGIN
SUSPEND;
NUM = :NUM + 1;
END
END
^

SET TERM ;
^

COMMIT WORK;

This is a selectable stored procedure. Every time suspend is called,
Firebird suspends the processing of the stored procedure while the
particular record is returned (ie the values inside the return
values). Use them just like a select statement

SELECT NUM
FROM GETNUMBERSBETWEEN(5,10);

Would return

NUM
===
5
6
7
8
9
10

You can read for yourself how to define an executable stored
procedure. Basically though, there is no suspend call, and you call
them like this

EXECUTE PROCEDURE MYPROCEDURE(5, 10);

Using triggers and stored procedures allows you to remove the
consistency check logic from the client application to the database
itself, and also reduce network traffic etc.

Adam