Elements of Procedures and Triggers - Firebird

PSQL module definitions are really a single SQL statement that begins with a CREATE clause and ends with a terminator. Within the module definition are a number of elements: clauses, keywords, blocks of multipl statements, branches, loops, and others. Some elements are mandatory; others are optional.

Although the complex definition of a PSQL module is a DDL statement, the SQL extensions within it are elements of a structured, high-level language that has certain distinctive rules. An important one to know about before you begin is the statement terminator.

The CREATE Statement

Source code for procedures and triggers is constructed inside a “super-statement” that begins with the keyword CREATE PROCEDURE or CREATE TRIGGER and ends with a terminator symbol following the final END statement, for example:

CREATE PROCEDURE Name...
...
AS
...
BEGIN
...
END ^

With both stored procedures and triggers, all statements following the keyword AS comprise the local variables (if any) and the logic of the program module. The main difference between triggers and stored procedures is in the header portion of the CREATE statement.

Statement Terminator

Each statement inside a stored procedure or trigger body—other than BEGIN and END—must be terminated b a semicolon. No other symbol is valid for terminating statements in PSQL. In DSQL, for both DML and DDL, the semicolon also happens to be the default statement terminator in Firebird and it is also the SQL standard for terminating statements.

This situation is going to present a logical problem for the parser that pre-compiles our PSQL modules: Which semicolons terminate statements inside the module and which one terminates the CREATE definition?

To get around this problem, Firebird has a switching SET TERM syntax that allows you to set a different external terminator, to be in effect for external statements while PSQL definitions are being parsed. In scripts, experienced developers often use a single SET TERM statement at the beginning of all scripts, to have their favorit alternative terminator in effect at all times during scripting. Some database admin tools support configuring their editors and metadata extraction programs with an alternative terminator.

SET TERM statements are used in isql and in scripts.

Isql pre-parses every statement and sends any terminated statement directly to the server as a single command. SET TERM is one of its own ISQL statements that it responds to not by sending a request to the server but by preparing its parser to interpret terminators differently. (Other ISQL SET statements also invoke special activity in the Isql program that is not meaningful outside isql.)

The DSQL layer does not recognize terminators for statements at all. Most of the other utilities that process scripts actually dispatch the DDL statements off to the server one by one without terminators. They provide parsing of their own to recognize the beginning and end of CREATE PROCEDURE statements and pass the internal semicolon terminators simply as regular symbols within the compound statement syntax.

When you use such a utility for creating PSQL modules interactively, it will usually throw exceptions at SET TERM since, as an SQL statement, it has no meaning outside isql. However, in scripts, these utilities usually parse for and expect a SET TERM statement and use the alternative terminator internally, in a manner equivalent to the way isql handles it.

So, use SET TERM in isql if you are using that tool to process your CREATE PROCEDURE statements interactively, and use it in scripts.

The alternative terminator can be any string symbol you like, except a semicolon, a space character, or a single -quote character. If you use an ordinary character, it will be case sensitive. It can be multiple characters if you prefer, including embedded spaces, and it must not be a reserved keyword. Both of the following statements are valid:

SET TERM ^; SET TERM boing! ;

In PSQL definitions, use semicolons for all internal statements except BEGIN and END, and use the alternative terminator for the final END statement:

... END ^

To return to “normal” statement termination, issue a second SET TERM statement that is the reverse of the first:

... END ^ COMMIT ^ SET TERM ;^

In Figure, the main elements of a PSQL module definition are split to illustrate the required elements of the module’s header and body sections. The mandatory parts are shaded.

Required elements of a PSQL module definition

Required elements of a PSQL module definition

Header Elements

The name of the procedure or trigger must be unique in the database.

For a trigger:

  • The keyword FOR and a table name, identifying the table that causes the trigger to fire
  • A mode (ACTIVE or INACTIVE)
  • A phase parameter (BEFORE or AFTER) that determines when the trigger fires
  • An event parameter (INSERT, UPDATE, DELETE)
  • Optionally, the keyword POSITION followed by an integer, indicating firing sequence

For a stored procedure:

  • An optional list of input parameters and their data types
  • If the procedure returns values to the calling program, a list of output parameters and their data types

Body Elements

For stored procedures and triggers:

  • The module body can begin with a list of one or more local variable declarations (name and SQL data type—no domains).
  • A block of statements in Firebird procedure and trigger language, bracketed by BEGIN and END. A block can itself include other blocks, so that there may be many levels of nesting.
  • Some embedded blocks can be handlers for exceptions occurring in preceding blocks. Such blocks are conditioned by a preceding WHEN predicate. Module-global exception handlers should follow all other embedded blocks.

Language Elements

Table shows the PSQL language elements available in Firebird.

PSQL Extensions for Stored Procedures and Triggers

PSQL Extensions for Stored Procedures and Triggers

PSQL Extensions for Stored Procedures and Triggers

PSQL Extensions for Stored Procedures and Triggers

PSQL Extensions for Stored Procedures and Triggers

PSQL Extensions for Stored Procedures and Triggers

PSQL Extensions for Stored Procedures and Triggers


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics