Section Navigation

Introducing the InterBase Procedure and Trigger Language

by Dan Ehrmann, Borland Developers Conference London 1997

Introduction

This presentation covers the basics of the InterBase stored procedure and trigger environment.

1. What is a Stored Procedure?

Stored procedures are programs that you store with an InterBase database. They allow you to directly manipulate the data in your database, without any client intervention, and without imposing network traffic. Unlike traditional programming environments such as Delphi and Paradox, where your programs run on a client machine, stored procedures are executed by the InterBase database server and run entirely on the server.

Each stored procedure is a standalone module of code that can be executed from the ISQL client utility, from another procedure or trigger, from an application such as Delphi or Paradox, or even as part of a SELECT statement, returning values as if the procedure were a subquery.

2. What is a Trigger?

A trigger is just like a stored procedure, but with one major difference: it fires automatically when certain types of events take place. These events are table- and row-based; for example, you can define a trigger that is invoked after a row is updated.

Procedures and triggers are usually created in an ASCII file which is then executed from the ISQL Client program within InterBase. This file contains statements to create the procedure or trigger as part of the database. Once the transaction is committed, the procedure or trigger is immediately available for use by client applications.

Procedures and triggers use a special programming language that is based on SQL. Included with InterBase, this language is optimized to manipulate data stored in tables. It has full access to SQL data manipulation language (DML) statements such as SELECT, INSERT, UPDATE and DELETE. It also provides extensions to SQL that support variables, comments, declarative statements, conditional testing, branching and looping. It is a language that is designed to run inside a database, against the data in that database.

3. Why Use Procedures and Triggers?

Procedures and triggers provide much of the intelligence and power for which InterBase in particular - and database servers in general - are reknowned.

They allow the server to perform complex operations on your databases without involving the client software.
They can be shared by every client application that accesses the database. You don't need to program the same logic into each client application; instead you just program and test it once on the server. And if the logic needs to change, you only need to change and test it once instead of many times.
They reduce network traffic by off-loading processing from application environments to the server itself. This is especially valuable for remote users who work through a slower connection to the database.
These tasks often execute faster on the server, since it is likely to be one of the most powerful machines on the network.
Stored procedures allow you to divide complex tasks into smaller and more logical modules. Stored procedures can be invoked from each other, allowing you to build up a set of standardized routines that are called in different ways.
Stored procedures are especially useful for performing complex periodic processing tasks, such as a month-end close or an archive operation.
Procedures provide better concurrency between the client and server. A user on a client machine can execute a procedure then do something else while the server processes the procedure.
Triggers in particular are a fundamental tool in maintaining the integrity of your database. For example, they can be defined to fire before a new row is inserted into the database. You would then use the trigger to determine if the row is complete and internally consistent, and if not, to actually deny the operation. You could also define a trigger to fire when the user attempts to delete a row from the database, where the table from which the row is being deleted is the master in a 1:Many relationship. The trigger could determine if there were linked rows in the detail table and delete them automatically or deny the operation.
Triggers are often used when a row is inserted to generate the primary key value. If a table is keyed using a generated id, you would call the GEN_ID( ) function in a trigger to create and store the unique identifier before posting the row.
Triggers can also be used to write status information on a primary data table to another table. For example, a trigger might be programmed to fire whenever a row in the Stock table changes, to compare the Quantity On Hand with the Quantity Ordered. If more product needs to be ordered, the trigger might call a procedure to insert a row into the Stock_ToBeOrdered table. Triggers can also be used to log changes to a history table, or to notify other applications that a change has taken place.
Triggers and procedures are an integral part of InterBase's Event Alerter mechanism. Events are normally reported to the event manager from within a trigger or procedure, based on the results of a query or logical test.
Finally, stored procedures offer enhanced security. They can perform operations on tables even if the user does not have the appropriate privilege for that operation on the table. All that is necessary is that the procedure have the privilege.

4. When to Use Stored Procedures?

Stored procedures should be used under the following circumstances:

If the operation can be done completely on the server, with no requirement to obtain information from the user while the operation is proceeding. Note that a stored procedure can accept input parameters when it is invoked, and it can pass return values back to the calling application.
If the operation requires processing a large number of rows, which would be prohibitively expensive in terms of network traffic to ship across the network to the client application.
If the operation is one that must be done periodically or frequently.
If the operation is performed by a number of different modules or processes within your application or by different applications.

It is possible to create a stored procedure that simply performs a SELECT statement and returns the result set. You might do this if you want to limit the columns or rows seen by the user. However, you can achieve the same effect with a view, especially if you SELECT on the view instead of the base table. Views have less overhead than stored procedures, and are a better solution for this type of problem.

5. When to Use Triggers?

As you will see below, triggers can be defined to fire before an operation such as an Insert, Update or Delete.

A "Before" trigger on an Insert or Update can determine if the row being inserted or updated is complete and internally consistent.
They can also be used to fill in columns that the user might not have direct access to, such as a unique ID for the table's primary key, a username or a Last Updated date.
"Before" triggers on a Delete query can return an error if there are linked detail rows in a referential integrity relationship. Alternatively, they can automatically delete the linked rows before allowing the delete operation on the master table to proceed.

Triggers can also be defined to fire after one of the same three operations.

An "After" trigger can be used to write an audit trail of changes made to a row. Triggers have access to the before and after values of each field in the row being changed.
They can also be used to update information in related tables. An example below demonstrates how to update a Qty_Used column in the Stock table, based on a Qty column in a LineItems table.

6. Creating Stored Procedures

Stored Procedures are normally created in a text file such as Windows Notepad. The file contains the necessary statements to connect to the database create or alter a procedure, and disconnect from the database. You can then use the InterBase Client for Windows or the ISQL command line utility to execute this file and save the stored procedure into the database itself.

Structure of a Stored Procedure

Use the CREATE PROCEDURE statement to describe a stored procedure. This statement has the following syntax. (For a formal definition, see the Language Reference.)

CREATE PROCEDURE ProcedureName
<parameter list>
RETURNS <return parameter list>
AS
<local variable declarations>
BEGIN
  <body of procedure>
END

"ProcedureName" follows the naming convention for any InterBase object, and must be unique.

Parameters

The parameter list represents a list of variables that are passed into the procedure from the calling application. These variables can be used within the procedure to modify its behavior. For example, if a procedure exists to process orders for a specific customer, one parameter might be the value of the CustomerNo to process.

The return parameter list represents values that the procedure can pass back to the calling application, such as the result of a calculation.

Each list is in the following format:

ParameterName1 ParameterType,
ParameterName2 ParameterType,
...
ParameterNameN ParameterType

Parameter Type is any valid InterBase data type except BLOB and arrays of datatypes.

Example

The following procedure declaration defines four variables that are passed into the procedure, and two variables passed back from the procedure to the calling application:

CREATE PROCEDURE SP_CalculateCustOrdersToDate
  ( CustomerID Integer, StartDate Date,
    EndDate Date, IncludeUnShipped Char(1) )
RETURNS ( OrderCount SmallInteger,
          OrderTotalValue Decimal( 8,2 ) )
AS
...;

Within the body of the procedure, these parameters will be used as variables to define the customer for which to search, the date range to use in the query, and whether or not unshipped orders should be included. The procedure calculates the count and total value of orders, assigning these numbers to the return parameters.

Local Variables

Within the body of the procedure, you can define variables which are local in scope. As with any other structured programming environment, these variables exist while the procedure is running. They are not visible outside the procedure and are destroyed when the procedure finishes.

Local variables are declared immediately after the AS clause, using the DECLARE VARIABLE statement. For example:

DECLARE VARIABLE IsCustomerActive CHAR(1);

You must declare each variable in its own DECLARE VARIABLE statement.

The Procedure Body

The body of the procedure starts with a BEGIN statement, which follows any local variable declarations. It ends with an END statement. BEGIN and END must also be used to surround any block of statements that logically belong together, such as the statements within a loop.

BEGIN and END do not have terminating characters, except for the final END within the procedure.

Here is the complete procedure example that we have built so far:

CREATE PROCEDURE SP_CalculateCustOrdersToDate
  ( CustomerID Integer, StartDate Date,
    EndDate Date, IncludeUnShipped Char(1) )
RETURNS ( OrderCount SmallInteger, OrderTotalValue Decimal( 8,2 ) )
AS
  DECLARE VARIABLE IsCustomerActive CHAR(1) );
BEGIN
  <body of procedure>
END

7. The Terminating Character Issue

When InterBase processes a script, it normally reads each statement in turn, processing that statement until the terminating character, which is normally a semicolon (;). Each statement is parsed, interpreted, converted to an internal representation and executed immediately.

This is not possible with stored procedures. The procedure definition includes SQL statements that are conceptually nested inside the CREATE PROCEDURE statement. InterBase needs to parse, interpret and convert the stored procedure to an internal representation that will be stored in the database.

But InterBase will not execute the procedure until you issue an EXECUTE PROCEDURE statement, or otherwise invoke the procedure.

In order to correctly parse and interpret a stored procedure, the database software needs a way to terminate the CREATE PROCEDURE which is different from how the individual statements inside the CREATE PROCEDURE are terminated.

This is accomplished with the SET TERM statement. It has the following syntax:

SET TERM <new terminating char(s)> <current terminating char(s)>

Example

The following example demonstrates how SET TERM is used:

SET TERM ^;
CREATE PROCEDURE Name
AS
BEGIN
  <procedure body statement>;
  <procedure body statement>;
  <procedure body statement>;
END ^
SET TERM ;^

Before the first SET TERM statement is processed, InterBase treats the semi-colon (;) as the statement terminating character. After the first SET TERM is executed, statements are terminated by the caret character (^).

InterBase then reads the next statement which is a CREATE PROCEDURE. It reads everything until the next occurrence of the ^ character, the new terminator. All of this is treated as a single statement. InterBase knows that a CREATE PROCEDURE indicates that the contents should be parsed, interpreted, converted to the internal representation and stored. No execution takes place.

Most importantly, InterBase ignores the semi-colons at the end of the embedded SQL statements within the CREATE PROCEDURE, until the procedure is actually executed.

The final statement is another SET TERM, to change the terminating character back to a semi-colon. This statement must be terminated by the revised terminating character, otherwise InterBase will not know where to find the end of the current statement. It's only after this statement has been parsed, interpreted and executed that the terminating character changes back to a semi-colon (;).

Note that you do not need to change the terminating character after each procedure definition. You can bracket a number of CREATE PROCEDURE statements between SET TERM statements.

8. Executing Stored Procedures

The simplest way to execute a stored procedure is using the EXECUTE PROCEDURE statement. This statement can be invoked in one of the following ways:

From the ISQL command line utility in immediate mode.
From the Windows ISQL client, also in immediate mode.
From within another stored procedure.
From within a trigger.
From an application program, such as Borland's Delphi tool.

When you execute a procedure from one of the ISQL clients, this statement has the following syntax:

EXECUTE PROCEDURE ProcedureName <parameter list>

If the procedure returns one or more values, these values are written to the ISQL Output window, as if generated by a traditional SELECT statement.

When you execute a procedure from within an InterBase application, such as another procedure or a trigger, it has the following syntax:

EXECUTE PROCEDURE ProcedureName <parameter list>
RETURNING_VALUES <parameter list>

If the procedure requires input variables, or if it returns output variables, you must specify the appropriate parameters. In each case, "parameter list" is a comma-separated list of parameters in the following format:

:Parameter1, :Parameter2, ..., ParameterN

Examples

This example demonstrates a simple stored procedure to calculate total sales in the Orders table. The simplest way to create the procedure is to use a text editor or Windows Notepad. The file should be saved with a .SQL extension.

CONNECT "SAMPLE.GDB" USER "SYSDBA" PASSWORD "masterkey";
SET TERM ^;
CREATE PROCEDURE SP_Calc_Sales
RETURNS (Total_Sales DECIMAL(8,2))
AS
BEGIN
  SELECT SUM(TotalInvoice)
  FROM Orders
  INTO :Total_Sales;
END^
SET TERM ;^

This script performs the following steps:

It starts by connecting to the sample database used for this presentation.
It next modifies the statement terminator so that the CREATE PROCEDURE is considered as a single statement.
It then creates a procedure called SP_Calc_Sales which returns a single decimal value.
The body of the procedure consists of a singleton SELECT statement. The resulting value is placed in the return parameter.
Finally the terminating character is changed back to the standard semi-colon.

Once the file has been created and saved, you should then run it in the ISQL Client for Windows program to define the procedure within the database. Then select File | Commit Work to complete the process of adding the procedure to the database.

To verify that it has been added, select View | Metadata Information... from the menu. Select Procedures from the drop-down menu, then type SP_CALC_SALES in the Object Name control. When you click OK, the procedure will be listed in the ISQL Output panel.

To test the procedure, move to the SQL Statement panel and enter the following statement:

EXECUTE PROCEDURE SP_Calc_Sales;

When you click on the Run button, the statement is echoed to the ISQL Output window, with the results of the procedure immediately following:

TOTAL_SALES
===========
345,678.90

Now let's modify the stored procedure to accept an input parameter. This parameter will be used in the WHERE clause of the query to limit the rows considered by the SUM( ) function.

CONNECT "SAMPLE.GDB" USER "SYSDBA" PASSWORD "masterkey";
SET TERM ^;
CREATE PROCEDURE SP_Calc_Sales2
  ( WhichCustomer INTEGER )
RETURNS (Total_Sales DECIMAL(8,2))
AS
BEGIN
  SELECT SUM(TotalInvoice)
  FROM Orders
  WHERE CustomerID = :WhichCustomer
  INTO :Total_Sales;
END^
SET TERM ;^

After you add this procedure to the database, enter and run the following statements:

EXECUTE PROCEDURE SP_Calc_Sales2 5515;
EXECUTE PROCEDURE SP_Calc_Sales2 1384;
EXECUTE PROCEDURE SP_Calc_Sales2;

The first two tests generate sales volume for the listed customer. The third test displays an error because a required parameter was not provided.

9. Additional Language Constructs

Within the procedure and trigger language, InterBase provides a number of additional language constructs and statements, described below.

The "Begin … End" Statements

In addition to defining the contents of the stored procedure, these keywords also delimit a block of statements that executes as a single statement. Do not place a semi-colon after either of these.

Comments

You can place comments anywhere inside a SQL script, including within a procedure. Use the following character sequences to delimit a comment:

/* comment goes here */

Comments can span multiple lines, but you cannot embed one comment inside another.

Assignment Statements

InterBase supports simple assignment statements in the following form:

variable = expression;

The "variable" above can be an input parameter, output parameter or a local variable defined in a DECLARE VARIABLE statement. A semi-colon must follow the expression. However, you do not need to precede the variable with a colon, as you must in a SELECT statement.

The following example uses different variables in calculating total cost for an item:

FinalCost = UnitCost
  + (UnitCost * SalesTaxRate)
  + ShippingCost;

Branching Using "IF…THEN…ELSE"

The syntax for the IF statement, a fundamental part of any language is as follows:

IF ( <conditional test> ) THEN
  <statement(s)>;
ELSE
  <statement(s)>;

"Conditional Test" is an expression that evaluates to logical True or False. If it's True, the statements in the THEN clause are executed; if False, the statements in the optional ELSE clause are executed. Important Note: parentheses are required around the conditional test.

You can use any of the standard comparison operators available in SQL, including the following:

Conditional Test	Notes
value=value	Equal to
value < value	Less than
value > value	Greater than
value <=value	Less than or equal to
value >=value	Greater than or equal to
value !< value	Not less than
value !> value	Not greater than
value <> value	Not equal to
value !=value	Not equal to
value BETWEEN value AND value	Within an inclusive range
value LIKE value	Wildcard search Use "%" for 0 or more chars. Use "_" for 1 char only.
value IN ( value, ...value)	One of the elements in a list
value IS NULL
value IS NOT NULL
value CONTAINING value	Case insensitive string match
value STARTING WITH value	Case sensitive string match

For example:

IF ( DesiredShippingMethod = 'DHL' ) THEN
IF ( TotalOrder > 1000 ) THEN
IF ( ShippingNotes CONTAINING 'urgent' ) THEN

In the table above, value can be a constant or one of the input parameters, output parameters or local variables used in the procedure. For example, if you define an input parameter as SpecialHandling, you might use the following IF test to determine if special handling is required for a specific stock item:

CREATE PROCEDURE SP_Calc_Shipping
  (ProductCost DECIMAL(8,2),
   DestinationZip CHAR(5),
   SpecialHandling CHAR(3) )
RETURNS
  (ShippingCost DECIMAL(8,2) )
AS
BEGIN
  ...
  IF ( SpecialHandling = 'Yes' ) THEN
  ...
  ELSE
...

If you place a single statement after the THEN or ELSE clauses, it should be terminated with a semi-colon. If you need to place multiple statements after one of these clauses, use the BEGIN and END keywords as follows:

IF <conditional test> THEN
BEGIN
  <statement1>;
  <statement2>;
  ...
  <statementN>;
END
ELSE
  <etc.>;

Looping Using "WHILE ... DO"

The WHILE ... DO statement provides a looping capability. The syntax for this statement is as follows:

WHILE ( <conditional test> ) DO
  <statement(s);

InterBase evaluates the conditional test. If it is True, the statements following the WHILE are executed. If it is False, the statements are skipped.

If you place a single statement after the DO clause, it should be terminated with a semi-colon. If you need multiple statements after one of these clauses, use the BEGIN and END keywords as described above. Parentheses around the conditional test are required.

The "SUSPEND" Statement

SUSPEND terminates procedure execution temporarily. When you use a SELECT statement inside a procedure and expect to return multiple rows, your application program needs to handle these rows one at a time. It normally does this by establishing a cursor on the result set and then FETCH'ing individual rows, one at a time.

When you use the SUSPEND statement inside such a procedure, you are telling the calling application that it should start fetching rows. This is normally done through the return parameters of the procedure, which are populated by the SUSPEND statement.

SUSPEND is not normally used in a procedure designed to be called with the EXECUTE PROCEDURE statement. Such procedures do not return rows, although they may populate return parameters.

The "EXIT" Statement

In both types of procedure, EXIT terminates procedure execution.

In a Select procedure, EXIT is used to terminate early, perhaps because a conditional test has been satisfied. In a procedure designed to be called with EXEECUTE PROCEDURE, EXIT indicates that the work of the procedure has been completed. It also assigns values to any return parameters, for passing back to the calling application.

For example, the following procedure allows you to specify a customer ID and the number of rows to return. It will then return the largest orders placed by that customer. This procedure implements a "Top-X" capability.

SET TERM ^;
CREATE PROCEDURE SP_Select_TopX_Orders
  (WhichCust INTEGER, HowMany SMALLINT )
RETURNS
  ( WhichOrd INTEGER, WhenSold DATE, HowBig FLOAT)
AS
  DECLARE VARIABLE i SMALLINT;
BEGIN
  i = 0;
  FOR SELECT OrderID, SaleDate, TotalInvoice
    FROM Orders
    WHERE CustomerID = :WhichCust
    ORDER BY TotalInvoice DESC
    INTO :WhichOrd, :WhenSold, :HowBig
  DO
  BEGIN
    SUSPEND;
    i = i + 1;
    IF ( i = :HowMany ) THEN
      EXIT;
  END
END^
SET TERM ;^

This procedure defines two input variables, the Customer ID and the number of rows to return, and three output variables representing basic data about the order. A local variable serves as a counter for the number of rows returned to the calling application.

The procedure uses a FOR SELECT statement, which is discussed in more detail in a subsequent section. It selects the necessary columns from the Orders table, ordering the results in descending order by the total invoice amount. The counter is used in conjunction with the SUSPEND statement to limit the number of rows returned. When this number has been reached, the EXIT statement terminates any further retrievals.

Using the ISQL Client for Windows, the following SELECT statement generates the top five orders placed by customer no. 1231.

SELECT * FROM SP_Select_TopX_Orders( 1231, 5 )

WHICHORD     WHENSOLD           HOWBIG
===========  ===========   ==============
1302         4-JUL-1994    27455.15
1073         14-APR-1992   21757.02
1360         27-OCT-1994   17587.94
1060         28-FEB-1992   17215.37
1278         30-APR-1994   13002.88

10. Using DML Statements with Procedures

"DML" is the SQL Data Manipulation Language, consisting primarily of the SELECT, INSERT, UPDATE and DELETE statements. You can use other SQL statements - such as CREATE VIEW - inside stored procedures, but there is generally no need to do so.

Using SELECT in a Procedure

The problem with using the SELECT statement inside a stored procedure is, what do you do with the results?

To solve this problem InterBase supports an extension to the standard SELECT statement. The INTO clause allows you to designate variables into which the results of the SELECT statement will be assigned. The syntax is as follows:

SELECT <result1>, <result2>, ..., <resultN>
FROM ...
WHERE ...
GROUP BY ...
INTO :Variable1, :Variable2, ..., VariableN;

The INTO clause must be the final clause in the SELECT statement. You must provide a variable for each result generated by the statement. An important limitation is that this form of the SELECT statement can generate only one row. (This explains why the ORDER BY clause makes no sense in this type of SELECT statement.)

Within a stored procedure, it is possible to use a SELECT that generates more than one row. To do this, you must use the FOR SELECT statement, which is described in more detail below.

Example

Suppose you need to extract the shipping date and method for a specific order. You might define the following stored procedure to perform this operation:

SET TERM ^;
CREATE PROCEDURE SP_Get_Shipping_Info
  (WhichOrder INTEGER)
RETURNS
  (WhenShipped DATE, ShipMethod VARCHAR(7) )
AS
BEGIN
  /* find the correct order and assign variables */
  SELECT ShipDate, ShipVia
  FROM Orders
  WHERE OrderID = :WhichOrder
  INTO :WhenShipped, :ShipMethod;
  EXIT;
END ^
SET TERM ;^

This procedure defines an input variable which specifies the order to locate, and two output variables that hold the date this order was shipped and the method used. The SELECT statement is used to determine this information, and this SELECT returns a single row and two columns. The returned values are placed directly into the output variables, which are then passed back to the calling application.

Using INSERT, UPDATE and DELETE

Wherever you specify a literal value in an INSERT, UPDATE or DELETE statement, you can substitute an input or local variable in place of this literal. For example, variables can be used for the values to be inserted into a new row, or the new values in an UPDATE statement. They can also be used in a WHERE clause, to specify the rows that are to be updated or deleted.

UPDATE Example

The following example assumes that a lineitem has been added or removed. It accepts the incremental change in the invoice amount as an input parameter, and updates the Orders table with a new StockTotal amount.

SET TERM ^;
CREATE PROCEDURE SP_Update_Stock_Total
  ( WhichOrder INTEGER, StockIncrement DECIMAL(8,2) )
AS
BEGIN
  UPDATE Orders
  SET StockTotal = StockTotal + :StockIncrement
  WHERE OrderID = :WhichOrder;
  EXIT;
END^
SET TERM ;^

The WhichOrder input variable is used in the WHERE clause and the StockIncrement input variable is used in the SET clause. Note that the incremental change in cost could be a positive amount to increase the invoice, or a negative amount to decrease it. Both situations are described below.

INSERT Example

The following example inserts a new row into the LineItems table for an existing order. It also calls the SP_Update_Stock_Total procedure just described, passing it the incremental change.

SET TERM ^;
CREATE PROCEDURE SP_Insert_LineItem
  ( WhichOrder INTEGER, WhichStock CHAR(10),
    CostOfEach DECIMAL(8,2), HowMany INTEGER )
AS
  DECLARE VARIABLE IntermediateTotal DECIMAL(8,2);
BEGIN
  /* save intermediate calculation */
  IntermediateTotal = CostOfEach * HowMany;
  /* insert new row */
  INSERT INTO LineItems
    VALUES( :WhichOrder, :WhichStock, :CostOfEach, :HowMany);
  /* call another procedure to update the master table */
  EXECUTE PROCEDURE SP_Update_Stock_Total
    :WhichOrder, :IntermediateTotal;
END^
SET TERM ;^

In this procedure, the unit cost and quantity variables are multiplied, with the result saved to an intermediate variable. The next step is a standard INSERT statement to insert the new row. Finally, the intermediate variable is passed to a nested procedure to update the matching total in the Orders master table.

To see this procedure in action, start by entering and running the following statement:

SELECT *
FROM LineItem
WHERE OrderID = 1060;

You will see a list of four lineitems for this order. Then enter and run the following statement:

SELECT TotalInvoice
FROM Orders
WHERE OrderID = 1060;

You will see the current invoice for this order, $15,355.00. Now enter and run this statement to insert a lineitem for this order:

EXECUTE PROCEDURE SP_Insert_LineItem
(1060, '1986', 25.00, 8 );

When you select from the LineItem table again, you will now see five items. When you select the TotalInvoice column from the Orders table again, the total invoice amount is now $200 higher.

DELETE Example

The following example performs a related function, removing a designated lineitem.

SET TERM ^;
CREATE PROCEDURE SP_Delete_LineItem
  ( WhichOrder INTEGER, WhichStock CHAR(10) )
AS
  DECLARE VARIABLE IntermediateTotal DECIMAL(8,2);
BEGIN
  /* calculate intermediate total               *
   * use -1 because we are subtracting lineitems */
  SELECT ( Total * -1 )
  FROM LineItems
  WHERE OrderID = :WhichOrder
    AND StockID = :WhichStock
  INTO :IntermediateTotal;
  /* delete the designated lineitem */
  DELETE FROM LineItem
  WHERE OrderID = :WhichOrder
    AND StockID = :WhichStock;
  /* update master record with new total */
  EXECUTE PROCEDURE SP_Update_Stock_Total
    :WhichOrder, :IntermediateTotal;
END^
SET TERM ;^

In the above example, Total is a computed column in the LineItems table. It is read directly and multiplied by -1 so that this amount will be subtracted from the total invoice. Once the LineItems row has been deleted, the total amount is passed to the same nested procedure described above, to update the matching total in the Orders master table.

To see this procedure in action, try deleting the lineitem just added in the previous example:

EXECUTE PROCEDURE SP_Delete_LineItem ( 1060, '1986' );

When you select from the LineItem table again, the order is back to four items. When you select the TotalInvoice column from the Orders table again, the total invoice amount is now $200 lower.

Another DELETE Example

The following procedure deletes a stock item from the database. It first checks to see if the item is included on any active orders. If it is, it will be flagged as being no longer available. If it is not included on any active orders, it will be deleted from the Stock table. It will also be deleted from the Stock_TBO table if found there.

SET TERM ^;
CREATE PROCEDURE SP_Delete_Stock
  (StockToDelete INTEGER)
RETURNS ( Results VARCHAR(50) )
AS
  /* Is the stock item on any active orders? */
 DECLARE VARIABLE InUse INTEGER;
BEGIN
  /* count how many active orders contain the item */
  SELECT COUNT( OrderID )
  FROM LineItem
  WHERE StockID = :StockToDelete
  INTO :InUse;
  IF ( InUse > 0 ) THEN
  /* if greater than zero, do not delete yet */
  BEGIN
    UPDATE Stock
    SET Available = 'N'
    WHERE StockID = :StockToDelete;
      Results = 'Item in use; flagged as unavailable';
  END
  ELSE
  /* if zero, delete it */
  BEGIN
    DELETE FROM Stock
    WHERE StockID = :StockToDelete;
      Results = 'Item no longer in use; deleted';
  END
  /* delete from the reorder table if it's there */
  DELETE FROM Stock_TBO
  WHERE StockID = :StockToDelete;
END^
SET TERM ;^

To test this procedure, enter and run the following statement:

EXECUTE PROCEDURE SP_Delete_Stock 1234;

You will see the message you defined to indicate that the specified stock item was deleted. This item does not appear on any orders. Now enter and run the following statement:

EXECUTE PROCEDURE SP_Delete_Stock 1330;

This time, you will see the message indicating that the specified stock item could not be deleted because it currently appears on one or more orders. Instead, it was flagged as being unavailable.

The "FOR SELECT ... DO" Statement

As noted above, it is possible to define a SELECT statement in a procedure that returns more than one row. InterBase provides the FOR SELECT statement to process these rows one at a time. This statement has the following syntax:

FOR <Select Statement>
DO
BEGIN
  <statement(s)>;
END

The SELECT statement must assign its output to local variables using the INTO operator described previously. When InterBase processes this statement it creates a cursor on the result set. The FOR … DO statement tells InterBase to move the cursor through the result set one row at a time, assigning each column to the designated local variable.

For example, suppose you want to survey sales for each product sold by a user-defined vendor. The following stored procedure generates a dynamic list of stock numbers, based on a vendor number supplied by the user. It passes each stock number in turn to a different procedure (not shown) to analyze sales for that item.

SET TERM ^;
CREATE PROCEDURE SP_Products_From_Vendor
  ( WhichVendor INTEGER )
AS
  DECLARE VARIABLE StockItem CHAR(10);
BEGIN
  /* extract list of stock items */
  FOR SELECT StockID
    FROM Stock
    WHERE VendorID = :WhichVendor
    INTO :StockItem
  DO
  BEGIN
    /* process sales for each stock item in turn */
    EXECUTE PROCEDURE SP_Analyze_Sales :StockItem;
  END
END^
SET TERM ;^

Here is another example. Suppose you decide to track the number of lineitems associated with the order. This information can always be derived from the database by performing a COUNT(*) query grouped on the OrderID. But for performance reasons, you might decide to maintain this information in a separate column in the Orders table, updating it in real time.

After you ALTER the Orders table to add the column, you need to populate this column. This operation might best be performed with the stored procedure shown below. It demonstrates the important point that not all stored procedures are used on an ongoing basis by the database application. Sometimes it is necessary to create a stored procedure to perform a "once-off" operation.

 SET TERM ^;
 CREATE PROCEDURE SP_Update_Lineitem_Count
 AS
   DECLARE VARIABLE OrderID INTEGER;
   DECLARE VARIABLE LineCount SMALLINT;
 BEGIN
   /* grab the count of lineitems for each order */
   FOR SELECT OrderID, COUNT(StockID)
     FROM LineItem
     GROUP BY OrderID
     INTO :OrderID, :LineCount
   DO
     /* write this count to the matching Orders row */
     UPDATE Orders
     SET ItemCount = :LineCount
     WHERE OrderID = :OrderID;
 END^
SET TERM ;^

Enter and run the following statement:

EXECUTE PROCEDURE SP_Update_LineItem_Count;

The procedure will run to completion, updating the ItemCount column in the Orders table. This may take a few moments. To test the results of this operation, enter and run the following statement to see the number of lineitems for each order:

SELECT OrderID, ItemCount
FROM Orders;

Calling a Procedure From a SELECT

InterBase also allows you to use a stored procedure in place of the table reference in a SELECT statement. This type of procedure is known as a "Select Procedure."

When you use a stored procedure in place of a table, the procedure should return multiple columns or rows. This allows the SELECT statement to further filter the results by different criteria.

The SUSPEND statement is used to suspend execution of the procedure and return the contents of the output variables back to the calling statement. If the stored procedure will return multiple rows, use the SUSPEND statement inside a FOR SELECT … DO loop to return all rows one at a time.

Consider the following stored procedure, which generates a list of stock items sold by a specified vendor, which also fall into a specified class.

SET TERM ^;
CREATE PROCEDURE SP_Find_Stock
  ( WhichVendor INTEGER, WhichClass VARCHAR(30) )
RETURNS
  ( StockID CHAR(10), Part VARCHAR(15),
    QtyOnHand INTEGER, Cost DECIMAL(8,2) )
AS
BEGIN
  /* query Stock table using input variables */
  FOR SELECT StockID, PartNumber, QuantityAvailable, ListPrice
    FROM Stock
    WHERE VendorID = :WhichVendor
      AND EquipmentClass = :WhichClass
    INTO StockID, Part, QtyOnHand, Cost
  DO
    SUSPEND;
END ^
SET TERM ;^

This procedure takes as its input parameters a vendor name and an equipment class. It uses these in a SELECT statement that returns matching stock items using the output parameters.

Because more than one row might be returned, the SELECT is placed inside a FOR SELECT … DO loop. InterBase generates the result set, and establishes a cursor on the first row. The cursor loops through each row issuing a SUSPEND statement, which takes the contents of that row and returns it to the calling statement or procedure.

The following statement invokes this stored procedure and displays the rows it returns:

SELECT *
FROM SP_Find_Stock( 3511, 'Tools');

A big advantage to using a stored procedure in this situation lies in the ability to further filter the results, using either selected columns or rows only. For example, the following statement displays only those stock items returned by the procedure that also cost more than $100:

SELECT *
FROM SP_Find_Stock( 2014, 'Small Instruments' )
WHERE Cost > 100;

The next example displays only selected columns from the result set:

SELECT Part, QtyOnHand
FROM SP_Find_Stock( 3820, 'Vehicle' );

You can also perform aggregates on the result set:

SELECT COUNT(StockID), AVG(ListPrice)
FROM SP_Find_Stock( 2674, 'Search Equipment' )
WHERE QtyOnHand > 5;

In all these examples, note that the returned column names are the names of the output parameters generated by the stored procedure.

11. Creating a Trigger

Triggers are created just like procedures. You write the trigger in a ASCII text editor such as Notepad, saving it as a SQL script file. From one of the ISQL client programs, you execute the instructions in this file to define the trigger in the database and save it as part of the database's metadata.

Use the CREATE TRIGGER statement to create a trigger in your database. This statement has the following general syntax. (For a formal definition, see the InterBase Language Reference.)

CREATE TRIGGER TriggerName
FOR TableName
<Trigger Type Keywords>
AS
<local variable declarations>
BEGIN
  <body of trigger>
END

As you can see, the structure of a CREATE TRIGGER statement is very similar to the structure for a CREATE PROCEDURE. "TriggerName" is a unique identifier within the database. The name follows the InterBase naming conventions used for columns, tables, views and procedures.

Triggers are assigned to a specific table or updatable view. They fire when a row-based operation takes place on the table. You must specify the table to which the trigger will be attached.

Active vs. Inactive

When a trigger is created, you can specify if it will immediately be active or inactive. "Active" is the default if one of these keywords is not specified.

This feature allows you to define triggers that are dependent on each other. It is also important when your database is under construction and only partially complete. You can define the shell of a trigger and make it initially inactive. Then, after you have defined the other triggers or procedures that are called by the new one or that cause the new one to be fired, you can go back and make the new one active.

Insert, Update or Delete

A trigger must be defined to fire on one of these keywords.

An "Insert" trigger fires when a row is inserted into the table.
An "Update" trigger fires when a row is modified in the table.
A "Delete" trigger fires when a row is deleted from the table.

If you want the same trigger to fire on more than one operation, you must define separate triggers for each operation. If they share any processing, place this code in a stored procedure and call the procedure from each trigger.

Before or After

A trigger must be defined to fire before or after the row-based operation. For example, a BEFORE INSERT trigger fires before a new row is actually inserted into the table; an AFTER INSERT trigger fires after the row has been inserted.

"Before" triggers are generally used for two purposes.

Such a trigger can be used to determine if you really want the operation to proceed. You might test certain parameters and determine that the row should not be inserted, updated or deleted. In this situation, you can raise an exception, allowing the client application to rollback the transaction.
Such a trigger can also be used to determine if there are linked rows that may be affected by the operation. For example, you might use a trigger to automatically delete lineitems before deleting the order master row. On the other hand, you might prohibit the user from deleting a stock item if there are active orders for that item.

"After" triggers are generally used to update columns in linked tables that depend for their values on the row being inserted, updated or deleted. For example, the TotalInvoice column in the Orders table would likely be maintained using AFTER INSERT, AFTER DELETE and AFTER UPDATE triggers on the Lineitem table.

The following trigger fires before InterBase allows a row to be inserted in the Lineitem table:

CREATE TRIGGER Insert_LineItem
FOR LineItem
ACTIVE BEFORE INSERT

The following trigger would normally fire after a row has been deleted, but it has been flagged as inactive until further notice.

CREATE TRIGGER Delete_Stock
FOR Stock
INACTIVE AFTER DELETE

Trigger Position

InterBase allows you to define more than one trigger of each type. For example, you can specify two BEFORE INSERT triggers for a particular table. Without some way to tell InterBase in what order the triggers should fire, InterBase will fire them in a random order that cannot be predicted in advance.

To solve this problem, InterBase provides the optional POSITION clause. Use POSITION to specify a sequence number which determines the firing order. Sequence numbers do not need to be consecutive; you might use 5,10 and 15, to give you room for later additions.

For example, in the following two trigger definitions, the second trigger fires before the first:

CREATE TRIGGER Test
FOR Orders
BEFORE DELETE POSITION 10
AS
...

CREATE TRIGGER AnotherTest
FOR Orders
BEFORE DELETE POSITION 5
AS
...

The trigger POSITION clause is used in two situations:

To create more modular programs. If you divide trigger actions into separate and smaller modules, you can more easily maintain and enhance this code.
To create different versions of the code. When you use the POSITION indicator in conjunction with the ACTIVE and INACTIVE keywords, you can tell InterBase to fire a newer or older version of a specific trigger.

Local Variables and the Trigger Body

Triggers use the same extensions to SQL that InterBase provides for stored procedures. Therefore, the following statements are also valid for triggers:

DECLARE VARIABLE
BEGIN ... END
SELECT ... INTO <:variable list>
Variable=Expression
/* comments */
EXECUTE PROCEDURE
FOR <select> DO ...
IF (<condition>) THEN ... ELSE ...
WHILE (<condition>) DO ...

The Terminating Character Issue

As with stored procedures, the CREATE TRIGGER statement includes SQL statements that are conceptually nested inside the CREATE TRIGGER statement. InterBase needs to parse, interpret and convert the trigger to an internal representation that will be stored in the database.

In order for InterBase to correctly parse and interpret a trigger, the database software needs a way to terminate the CREATE TRIGGER which is different from how the individual statements inside the CREATE TRIGGER are terminated.

As with procedures, this is accomplished with the SET TERM statement

12. The NEW. and OLD. Variables

In triggers (but not stored procedures), InterBase provides two system variables that maintain information about the row being inserted, updated or deleted:

OLD.columnName refers to the current or previous values in a row being updated or deleted. It is not relevant for insert triggers.
NEW.columnName refers to the new values in a row being inserted or updated. It is not relevant for delete triggers.

You can read from or write to these trigger variables.

Reading Values

When you use one of these variables on the right side of an assignment statement, you are reading from them. For example:

Hold_Old_TotalInvoice = OLD.TotalInvoice

You can also read values from a trigger variable as part of a conditional expression. For example:

IF ( OLD.Quantity <> NEW.Quantity ) THEN
EXECUTE PROCEDURE UpdateTotalInvoice ( NEW.Quantity );

Finally, you can use these variables as part of an SQL statement such as SELECT, INSERT or UPDATE. For example, suppose you want to move fully paid orders to an archive table. You might implement the following statement as part of a BEFORE DELETE trigger on the Orders table:

INSERT INTO OrdersArchive
VALUES ( OLD.OrderNo, OLD.CustomerNo, OLD.SaleDate,
... );

Writing Values

You can also explicitly modify a trigger variable, although this is only useful in very limited contexts. It makes little sense to write to the OLD variable, since it just holds the original values from the row being updated. And it makes little sense to write to the NEW variable in an AFTER trigger, since by this time, the change has already been written.

However, there are situations where you might write to a NEW variable in a BEFORE trigger, to modify a value being inserted or updated before that value is written to the table.

For example, the following extract from a BEFORE INSERT trigger modifies the ShippingCost value based on the size of the order:

IF ( NEW.TotalInvoice >= 1000 ) THEN
  NEW.ShippingCost = 0;

Important

If you change a value as shown above, the modified value will not be visible to other BEFORE triggers. The change does not take place in the row itself until after the transaction is committed to the table.

Generators

When your tables are keyed on a sequential ID number, an important function of the BEFORE INSERT trigger is to generate a new ID and assign it to the appropriate column. This is done with a generator.

When you define the database, you can create a generator for each ID value using the CREATE GENERATOR statement. You can assign an initial value to the generator using the SET GENERATOR statement. If you don't use SET GENERATOR, the initial value will be 0.

To increment the generator and grab the new value, use the GEN_ID( ) function. In a trigger, the return value of the GEN_ID( ) function would be assigned to a NEW. Variable, representing the unique ID column.

Example: Adding an Order and a Lineitem

SET TERM ^;
CREATE TRIGGER TR_Insert_OrderID
  FOR Orders
  ACTIVE BEFORE INSERT
AS
BEGIN
  /* grab next ID, increment generator,
     and assign to the primary key column */
  NEW.OrderNo = GEN_ID ( Gen_OrderID, 1 );
END ^
SET TERM ;^

Once you have added this trigger to the database's metadata, and verified that it is there, the next step is to test it. In the SQL Statement panel enter and run the following statement:

INSERT INTO Orders
  (CustomerID, SaleDate, ShipVia, Terms,
   ShippingCost, StockTotal, ItemCount)
  VALUES (2135, 'Today', 'UPS', 'Net 30', 0, 0, 0);

To test if the statement was successful, enter and run the following statement. You should see the new order with a generated value in the OrderID column.

SELECT *
FROM Orders
WHERE SaleDate = 'Today';

The next example updates the Orders table when new rows are inserted into the Lineitem table. This trigger will update the StockTotal and ShippingCost columns of the Orders table.

SET TERM ^;
CREATE TRIGGER TR_Insert_LineItem
  FOR LineItem
  ACTIVE AFTER INSERT
AS
BEGIN
  UPDATE Orders
    SET StockCount = StockCount + NEW.Quantity,
    StockTotal = StockTotal + (New.Quantity * NEW.SellingPrice)
    WHERE OrderID = NEW.OrderID;
END^
SET TERM ;^

To test this trigger, enter and run the following statement:

SELECT OrderID
FROM Orders
WHERE SaleDate = 'Today';

You will see the OrderNo for the order inserted in the preceding exercise. Note this number. Enter and run the following statement. In place of the <NewOrderID>, enter the number you just noted.

INSERT INTO LineItem
  (OrderID, StockID, SellingPrice, Quantity)
VALUES ( <NewOrderID>, 7612, 100.00, 5 );

Enter and run the following statement. Substitute the new order number again.

SELECT *
FROM Lineitem
WHERE OrderID = <NewOrderID>;

You will see the new lineitem just inserted. Enter the run the following statement. Substitute the new order number.

SELECT *
FROM Orders
WHERE OrderID = <NewOrderID>;

Scroll to the right to read the values in the StockTotal column (500.00) and StockCount column (5).

13. Trigger Chaining

It is not uncommon for one trigger to perform an operation that causes another trigger to fire, and that operation in turn fires another trigger. This is known as chaining.

Consider the following examples:

The user inserts a new lineitem for an order. This fires an AFTER INSERT trigger to reduce the value of the QuantityAvailable column in the Stock table.
This update operation in turn fires an AFTER UPDATE trigger on the Stock table which tests to see if the QuantityAvailable is now below the ReOrderLevel value. If it is, a new row is inserted into the Stock_TBO table.

In addition, the same operation might fire triggers in a different direction:

The user inserts a new lineitem for an order. This fires another AFTER INSERT trigger to update the TotalInvoice column of the Orders table.
This update operation in turn fires an AFTER UPDATE trigger on the Orders table which inserts a new row in an OrdersChange history table, indicating that the TotalInvoice changed on a particular date.

Example: Trigger Chaining

This example demonstrates a trigger that, when it fires, causes another trigger to fire in turn.

The following trigger, which fires after a row has been inserted into the Lineitem table, updates the QuantityAvailable column of the Stock table, to deduct the quantity of the item just ordered.

SET TERM ^;
CREATE TRIGGER TR_Insert_LineItem_Stock
  FOR LineItem
  ACTIVE AFTER INSERT
  POSITION 10
AS
BEGIN
  /* Deduct quantity just ordered from STOCK */
  UPDATE Stock
    SET QuantityAvailable = QuantityAvailable - NEW.Quantity
    WHERE StockID = NEW.StockID;
END^
SET TERM ;^

The next trigger is defined on the Stock table to fire after an update. It tests QuantityAvailable against the ReOrderLevel column. If the on hand quantity has dropped below the designated reorder level, this trigger will insert a new row into the Stock_TBO ("To Be Ordered") table.

SET TERM ^;
CREATE TRIGGER TR_Update_Stock
  FOR Stock
  ACTIVE AFTER UPDATE
AS
  DECLARE VARIABLE IsStock SMALLINT;
BEGIN
  /* test if reorder is even necessary */
  IF (NEW.QuantityAvailable <= NEW.ReOrderLevel) THEN
  BEGIN
    /* test if stock item is already in table */
    SELECT COUNT(*)
    FROM Stock_TBO
    WHERE StockID = NEW.StockID
    INTO :IsStock;
    IF (IsStock = 0) THEN
      /* if not, insert a new row */
      INSERT INTO Stock_TBO (StockID, Quantity, OrderDate)
      VALUES (NEW.StockID, 20, 'Today' );
    ELSE
      /* if it is, update existing quantity */
      UPDATE Stock_TBO
      SET Quantity = Quantity + 20,
        OrderDate = 'Today'
      WHERE StockID = NEW.StockID;
    END
END^
SET TERM ;^

To test this trigger, enter and run the following statement:

SELECT OrderID
FROM Orders
WHERE SaleDate = 'Today';

You will see the OrderID for the order inserted in the preceding example. Note this number. Now, enter and run the following statement. In place of the <NewOrderNo>, enter the number you just noted.

INSERT INTO Lineitem
VALUES ( <NewOrderNo>, 1234, 5, 100.00 );

Enter and run the following statement.

SELECT * FROM Stock_TBO;

You will see a new row in this table for the item # 1234, indicating that this item needs to be reordered.

Enter and run the following statement. Substitute the new order number.

INSERT INTO Lineitem
VALUES ( <NewOrderNo>, 4515, 20, 35.00 );

Enter and run the following SELECT statement again:

SELECT *
FROM Stock_TBO;

Notice this time that item # 4515, which was already in the database, has had its reorder quantity increased by 20 items.

14. Altering Procedures and Triggers

Procedures

To modify a stored procedure, use the ALTER PROCEDURE statement. This statement has the following syntax:

ALTER PROCEDURE ProcedureName
<revised parameter list>
RETURNS <revised return parameter list>
AS
<local variable declarations>
BEGIN
<body of procedure>
END

When you use ALTER PROCEDURE, you replace the complete original definition of the procedure, specifying new input and output parameters, local variables and the procedure body. This allows you to modify a procedure in place, without being concerned about procedures called from the one you are modifying and about other procedures which call the one you are modifying.

InterBase itself uses ALTER PROCEDURE to document the metadata for a database. It first defines the procedure name using CREATE PROCEDURE as follows:

CREATE PROCEDURE ProcedureName
AS
BEGIN
EXIT
END^

It then uses ALTER PROCEDURE to modify this shell with the actual parameters and body of the procedure. This is done because procedures can call other procedures. If the procedure definition references another procedure, the second procedure must exist, even if only as a shell.

A procedure can only be altered by the original creator or by the SYSDBA user.

Triggers

To modify an existing trigger, use the ALTER TRIGGER statement. This statement can be used in one of two ways:

Modify Trigger Header Only

You can use ALTER TRIGGER to modify the trigger header only, leaving the body of the trigger unchanged. The most common uses of this option are to change the active status of the trigger, and to change its position relative to other triggers of the same type.

For example, the following statement makes the specified trigger inactive:

ALTER TRIGGER Update_Order_History INACTIVE;

The following example changes the position of a trigger from 5 to 15. If another trigger was assigned to position 10, it would now be processed before this one instead of after.

ALTER TRIGGER Insert_Stock_Item POSITION 15;

It is not common to change the trigger operation or time relative to the transaction. If you designed a trigger to fire after a row is deleted from a table, you are not likely to need to change this trigger so that it fires before the row is deleted, unless you originally made a mistake.

Modify Trigger Body

You can use the ALTER TRIGGER statement to modify the actions performed by a trigger. When you do this, you can leave out all header information from the statement, using the following syntax:

ALTER TRIGGER TriggerName
AS
BEGIN
<new trigger body>
END

The new trigger body completely replaces the old one.

Note that you can also change the header and body at the same time. When you need to do this, it is probably easier to drop the trigger and recreate it from scratch.

15. Dropping Procedures and Triggers

Procedures

To remove a procedure from the database's definition, use the DROP PROCEDURE statement. It has the following syntax:

DROP PROCEDURE ProcedureName;

A procedure can only be dropped by the original creator or by the SYSDBA. If the procedure is currently in use, or if it is referenced in another procedure, trigger, exception, view or SELECT statement, it cannot be dropped until this dependency is removed.

To view procedure dependencies in the ISQL command-line utility, issue the command

SHOW PROCEDURES

To view procedure dependencies in the ISQL Client for Windows, select View | Metadata Information… from the menu. Select "Procedures" from the drop-down list and press [Enter] without entering a specific procedure name. You will see a list of procedures in the database, together with the dependent object's name and type.

Triggers

Use the DROP TRIGGER statement to drop a trigger. It has the following syntax:

DROP TRIGGER TriggerName;

You cannot drop a trigger if other users of the database are currently making changes on the table to which the trigger belongs. If they are inserting, updating or deleting rows from this table, the trigger may be firing on a regular basis.

If you need to suspend a trigger, but expect that you might need it again in the future, use ALTER TRIGGER … INACTIVE instead. It leaves the trigger in the database, but disables it from firing.

16. Events, Exceptions, Errors and Transactions

Events

InterBase's event alerter mechanism relies heavily on procedures and triggers to fire events in which an application program has registered interest.

For example, a Delphi application might need to know if the outstanding balance for a customer exceeds $10,000. The InterBase Event Manager will then log the application as being interested in this event, and will notify the application if the event takes place. But how does it know that the event has taken place?

This is done by posting an event from within a procedure or trigger, using the POST_EVENT statement. Suppose that you have a column in the Customer table showing the current balance. An AFTER UPDATE trigger on this table might test the value of the column as follows:

SET TERM ^;
CREATE TRIGGER TR_Customer_Balance
  FOR Customer
  ACTIVE AFTER UPDATE
AS
BEGIN
  IF ( CustomerBalance > 10000 ) THEN
    POST_EVENT "High_Customer_Balance";
END^
SET TERM ;^

Exceptions

Exceptions are "named error messages" that are stored in the database for use by stored procedures and triggers. An exception is invoked when you raise it.

Exceptions can be shared among the different modules of your application, and even among different applications that share the same database. They provide a simple way to standardize how the various modules and applications handle pre-programmed data errors.

To create an exception, use the following statement:

CREATE EXCEPTION StockInUse;

Exceptions are generally not used for database errors such as primary key violations or missing tables. These types of errors are best handled by SQL Error codes and InterBase Error codes, discussed below.

Instead, exceptions are used to implement program logic. For example, if you will not allow users to delete a stock item that is currently included on active orders, you might implement an exception that reports this error condition. When the user executes a stored procedure to delete such a stock item, the procedure can raise the exception, indicating that this stock item should not and will not be deleted.

SET TERM ^;
CREATE PROCEDURE SP_Delete_Stock
  ( WhichStock Integer )
AS
  DECLARE VARIABLE Is_Stock Integer
BEGIN
  /* query for Stock item in use */
  SELECT COUNT(*)
  FROM Lineitem
  WHERE StockID = :WhichStock
  INTO Is_Stock;
  /* test the local variable */
  IF ( Is_Stock > 0 ) THEN
    EXCEPTION StockInUse;
  ELSE
    /* continue here to delete the stock item */
    <etc.> ...
END^
SET TERM ;^

Errors

On occasion, your procedures and triggers need to handle errors generated by InterBase, either from SQL operations against the database, or engine-level errors.

Within InterBase, errors are categorized using a unique identifier. For example error 335544351L represents an unsuccessful metadata update, while error 335544674L occurs if you try to delete the last remaining column in a table. InterBase allows you to test for one of these errors either of two ways: using a code number which is standard across many SQL implementations, or using an InterBase constant to represent the number.

Testing SQLCODE

After each SQL statement executes, InterBase returns an error code to the SQLCODE system variable. This code can be interrogated by your application to determine if an error occurred. The following values might be found:

SQL Code	Description
0	Statement completed successfully.
1 - 99	Warning or informational message
100 +	End of File reached in FOR SELECT ... DO
< 0	Error: the statement did not complete.

A complete list of SQLCODE numbers can be found in Appendix B of the InterBase Language Reference manual (spanning more than 12 pages!)

Many of these errors are very obscure and not likely to be found in a running database that is being correctly administered. Some of these errors are returned by InterBase as you define the metadata of the database, and not when your database and programs are actually in operation.

For example, if you attempt to grant privileges on a database object to another user, and you were not the owner of the object, nor were you granted WITH GRANT OPTION privileges from the owner, you will receive the following error message:

Statement Failed, SQLCODE = -607
Unsuccessful metadata update
No grant option for privilege on table/view <tablename>

If you look in Appendix B, SQLCODE -607 is listed with the text "Unsuccessful metadata update." A number of other errors also have this SQLCODE, but the specific message differentiates the various "-607" conditions.

Testing GDSCODE

Within embedded applications, you should test for errors using InterBase-specific constants to represent the internal number. For example, the following constants represent the two numbers cited at the beginning of this section:

Internal ID	GDSCODE	SQLCODE	General Description
335544351L	isc_no_meta_update	-607	unsuccessful metadata update
335544674L	isc_del_last_field	-616	cannot delete last column

Appendix B of the InterBase Language Reference also contains a list of InterBase error constants and the meaning of each, stretching across more than 14 pages!

Using the WHEN … DO Statement

InterBase provides a statement in its procedure and trigger language to intercept errors without returning an error code to the calling application. This is the WHEN … DO statement.

For example, the following statement traps for a primary key violation on a row insert:

WHEN SQLCODE -803 DO
BEGIN
<code to handle invalid PV>;
END;

The WHEN statement must the last statement in the BEGIN … END block for whose errors it is designed to trap.

Transactions

Unlike some other database servers, InterBase does not allow you to begin, commit or rollback transactions from within a stored procedure or trigger.

Instead, procedures work within the context of a transaction which must be started before the procedure is executed. If the procedure completes normally, the calling application will commit the transaction. If the procedure was not able to commit, it should return an exception, allowing the calling application to rollback the transaction.

Triggers also work within the context of a transaction. Because the trigger is fired as a result of an INSERT, UPDATE or DELETE statement, the operations performed by the trigger must be considered as part of the same transaction as the SQL statement. If a trigger encounters a problem, it too will raise an exception, causing the rest of the trigger to be terminated and undoing any changes made by the trigger.

When a transaction is rolled back, every change performed by that transaction will be rolled back. This is true even if the original statement causes changes to many tables, firing four or five triggers and a number of stored procedures in the process!

Sign In

Section Navigation

Introducing the InterBase Procedure and Trigger Language

Introduction

1. What is a Stored Procedure?

2. What is a Trigger?

3. Why Use Procedures and Triggers?

4. When to Use Stored Procedures?

5. When to Use Triggers?

6. Creating Stored Procedures

Structure of a Stored Procedure

Parameters

Example

Local Variables

The Procedure Body

7. The Terminating Character Issue

Example

8. Executing Stored Procedures

Examples

9. Additional Language Constructs

The "Begin … End" Statements

Comments

Assignment Statements

Branching Using "IF…THEN…ELSE"

Looping Using "WHILE ... DO"

The "SUSPEND" Statement

The "EXIT" Statement

10. Using DML Statements with Procedures

Using SELECT in a Procedure

Example

Using INSERT, UPDATE and DELETE

UPDATE Example

INSERT Example

DELETE Example

Another DELETE Example

The "FOR SELECT ... DO" Statement

Calling a Procedure From a SELECT

11. Creating a Trigger

Active vs. Inactive

Insert, Update or Delete

Before or After

Trigger Position

Local Variables and the Trigger Body

The Terminating Character Issue

12. The NEW. and OLD. Variables

Reading Values

Writing Values

Generators

Example: Adding an Order and a Lineitem

13. Trigger Chaining

Example: Trigger Chaining

14. Altering Procedures and Triggers

Procedures

Triggers

Modify Trigger Header Only

Modify Trigger Body

15. Dropping Procedures and Triggers

Procedures

Triggers

16. Events, Exceptions, Errors and Transactions

Events

Exceptions

Errors

Testing SQLCODE

Testing GDSCODE

Using the WHEN … DO Statement

Transactions