InterBase Procedure and Trigger Language

Go Up to Creating Triggers

InterBase procedure and trigger language is a complete programming language for stored procedures and triggers. It includes:

• SQL data manipulation statements: INSERT, UPDATE, DELETE, and singleton SELECT.
• SQL operators and expressions, including UDFs that are linked with the database server and generators.
• Powerful extensions to SQL, including assignment statements, control-flow statements, context variables, event-posting statements, exceptions, and error-handling statements.

Although stored procedures and triggers are used in entirely different ways and for different purposes, they both use procedure and trigger language. Both triggers and stored procedures may use any statements in procedure and trigger language, with some exceptions:

• Context variables are unique to triggers.
• Input and output parameters, and the SUSPEND and EXIT statements which return values are unique to stored procedures.

The stored procedure and trigger language does not include many of the statement types available in DSQL or gpre. The following statement types are not supported in triggers or stored procedures:

• Data definition language statements: CREATE, ALTER, DROP, DECLARE EXTERNAL FUNCTION, and DECLARE FILTER
• Transaction control statements: SET TRANSACTION, COMMIT, ROLLBACK
• Dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE
• CONNECT/DISCONNECT, and sending SQL statements to another database
• GRANT/REVOKE
• SET GENERATOR
• EVENT INIT/WAIT
• BEGIN/END DECLARE SECTION
• BASED ON
• WHENEVER
• DECLARE CURSOR
• OPEN
• FETCH

The following table summarizes the language extensions for triggers: 

Procedure and trigger language extensions

Statement	Description
BEGIN … END	Defines a block of statements that executes as one. The BEGIN keyword starts the block; the END keyword terminates it. Neither should be followed by a semicolon.
<variable> = <expression>	Assignment statement which assigns the value of <expression> to local variable, <variable>.
/* comment_text */	Programmer’s comment, where <comment_text> can be any number of lines of text.
EXCEPTION <exception_name>	Raises the named exception. An exception is a user-defined error, which returns an error message to the calling application unless handled by a WHEN statement.
EXECUTE PROCEDURE <proc_name>    [<var> [, <var> …]] [RETURNING_VALUES<br/><   var> [, <var> …]]	Executes stored procedure, <proc_name>, with the listed input arguments, returning values in the listed output arguments. Input and output arguments must be local variables.
FOR <select_statement> &nbsp;&nbsp;&nbsp;DO <compound_statement>	Repeats the statement or block following DO for every qualifying row retrieved by <select_statement>. <select_statement>: a normal SELECT statement, except the INTO clause is required and must come last.
<compound_statement>	Either a single statement in procedure and trigger language or a block of statements bracketed by BEGIN and END.
IF (<condition>) &nbsp;&nbsp;&nbsp;THEN <compound_statement>    [ELSE <compound_statement>]	Tests <condition>, and if it is TRUE, performs the statement or block following THEN, otherwise performs the statement or block following ELSE, if present. <condition>: a Boolean expression (TRUE, FALSE, or UNKNOWN), generally two expressions as operands of a comparison operator.
NEW.<column>	New context variable that indicates a new column value in an INSERT or UPDATE operation.
OLD.<column>	Old context variable that indicates a column value before an UPDATE or DELETE operation.
POST_EVENT <event_name>	Posts the event, <event_name>.
WHILE (<condition>)   DO <compound_statement>	While <condition> is TRUE, keep performing <compound_statement>. First <condition> is tested, and if it is TRUE, then <compound_statement> is performed. This sequence is repeated until <condition> is no longer TRUE.
WHEN   {<error> [, <error> …]|ANY}   DO <compound_statement>	Error-handling statement. When one of the specified errors occurs, performs <compound_statement>. WHEN statements, if present, must come at the end of a block, just before END. <error>: EXCEPTION <exception_name,> SQLCODE <errcode> or GDSCODE <number.> ANY: handles any errors.

Topics

• Syntax Errors in Triggers