Introduction to InterBase Stored Procedures and Triggers

Comments

You can place comments anywhere in a stored procedure using the /* this is a comment */ syntax. A comment can span multiple lines but one comment cannot be nested inside another.

Compound Statements (BEGIN/END)

The procedure and trigger language resembles Pascal in that constructs such as if … then … else and while loops can only contain a single statement. However, the keywords BEGIN and END can be used to enclose several statements and form a single compound statement. Do not place a semicolon after either BEGIN or END.

Assignment Statements

The procedure and trigger language supports assignment statements of the form

Var1 = Var2 * Var3;

where Var1 is either a local variable or an output parameter and Var2 and Var3 are either local variables or input parameters. The expression on the right side of the assignment can be as complex as you wish and you can use parentheses to group operations to any depth.

If…Then…Else

The syntax of the InterBase IF statement is:

IF <conditional expression> THEN
  <statement>
ELSE
  <statement>

where <statement> can be either a single statement or multiple statements enclosed in a BEGIN/END block. In addition to the usual logical cooperators (=, <, >, <=, >=, <>) you can also use the following SQL operators in the conditional expression.

Examples of valid IF statements are:

IF (any_sales > 0) THEN
BEGIN
  EXCEPTION reassign_sales;
  SUSPEND;
END

IF (first IS NOT NULL) THEN
  line2=first || ' ' || last;
ELSE
  line2=last;

Note in the example above that the string concatenation operator in the InterBase procedure and trigger language is || (a double vertical bar) not the + that is used in many programming languages.

IF (:mngr_no IS NULL) THEN
BEGIN
  mngr_name='--TBH--';
  title='';
END
ELSE
  SELECT full_name, job_code
  FROM employee
  WHERE emp_no=:mngr_no
  INTO :mngr_name, :title;

While…Do

The WHILE…DO statement provides for looping in stored procedures and triggers. The syntax is:

WHILE (<conditional expression>) DO <statement>

where <statement> can be a compound statement enclosed in a BEGIN/END pair. Note that the parentheses around the conditional expression are required. For example:

WHILE (i <=5) DO
BEGIN
  SELECT language_req[:i] FROM job
  WHERE ((job_code=:code) AND (job_grade=:grade) AND (job_country=:cty)
    AND (language_req IS NOT NULL))
  INTO :languages;
  IF (languages=' ') THEN /* Prints 'NULL' instead of blanks */
    languages='NULL';
  i=i +1;
  SUSPEND;
END

Using SQL Statements In Stored Procedures

You can use SQL SELECT, INSERT, UPDATE and DELETE statements in stored procedures exactly as you would in a query with only minor changes in syntax. For all of these statements you can use local variables or input parameters in any place that a literal value is allowed. For example, in the following INSERT statement the inserted values are supplied by the input parameters.

CREATE PROCEDURE ADD_EMP_PROJ(
  EMP_NO SMALLINT,
  PROJ_ID CHAR(5)
)
AS
BEGIN
  BEGIN
    INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
  WHEN SQLCODE -530 DO
    EXCEPTION unknown_emp_id;
  END
  SUSPEND;
END ^

The second difference is the addition of the INTO clause to the SELECT statement so you can select values directly into variables or output parameters as shown in the following example.

CREATE PROCEDURE CUSTOMER_COUNT
RETURNS (
  CUSTOMERCOUNT INTEGER
)
AS
BEGIN
  SELECT COUNT(*) FROM CUSTOMER INTO :CustomerCount;
  SUSPEND;
END ^

You cannot use SQL DDL statements in stored procedures. This restriction applies to CREATE, ALTER, DROP, SET, GRANT, REVOKE, COMMIT and ROLLBACK.

Using FOR SELECT…DO

The preceding example of a SELECT statement that selects one or more values into variables is fine as long as the SELECT returns a single row. When you need to process multiple rows returned by a SELECT you must use the FOR SELECT…DO statement as shown below.

CREATE PROCEDURE ORDER_LIST(
  CUST_NO INTEGER
)
RETURNS (
  PO_NUMBER CHAR(8)
)
AS
BEGIN
  FOR SELECT PO_NUMBER FROM SALES
    WHERE CUST_NO=:CUST_NO
    INTO :PO_NUMBER
  DO
    SUSPEND;
END ^

This procedure takes a customer number as its input parameter and returns all of the purchase order numbers for that customer from the sales table. Note that the purchase order numbers are all returned in a single output variable. Here is how it works. The FOR keyword tells InterBase to open a cursor on the result set of the SELECT statement. The SELECT statement must include an INTO clause that assigns each field returned by the select to a local variable or output parameter. The statement following the keyword DO is executed for each row returned by the SELECT. The statement following DO can be a compound statement enclosed in a BEGIN/END block.

Using Suspend

In the example above, the SUSPEND command tells the stored procedure to suspend execution until a fetch is received from the client then load the first project id into the output parameter and return it to the client. Each time the client issues a fetch the next project id is loaded into the output parameter and returned to the client. This continues until all of the rows returned by the SELECT have been processed. SUSPEND is not used just with FOR SELECT. It is used anytime a stored procedure returns values to the client to prevent the stored procedure from terminating until the client has fetched the results. The following is a very simple example of a procedure that returns a single value in an output parameter.

CREATE PROCEDURE CUSTOMER_COUNT
RETURNS (
  CUSTOMERCOUNT INTEGER
)
AS
BEGIN
  SELECT COUNT(*) FROM CUSTOMER INTO :CustomerCount;
  SUSPEND;
END ^

Handling the Semicolon Conundrum

IBConsole identifies the end of each statement in a SQL script by its terminating character which is, by default, a semicolon. That works fine in most cases but not when creating stored procedures and triggers. The problem is that you want IBConsole to execute the CREATE PROCEDURE statement as a single statement and that means it must end with a semicolon. However, each of the statements in the body of the procedure also ends with a semicolon and IBConsole thinks it has encountered the end of a statement whenever it sees a semicolon. The only solution is to change the terminating character that IBConsole looks for to identify the end of the statement to something other than a semicolon using the SET TERM statement. The following script shows how.

SET TERM ^ ;
CREATE PROCEDURE Customer_Count
RETURNS (
  CustomerCount Integer
)
AS
BEGIN
  SELECT COUNT(*) FROM CUSTOMER
  INTO :CustomerCount;
  SUSPEND;
END ^
SET TERM ; ^

The first SET TERM statement changes the terminating character to a caret (^). Note that this statement must end with a semicolon because that is the terminating character until the SET TERM ^ ; statement executes. IBConsole will now ignore the semicolons at the end of the statements in the body of the stored procedure. A caret is placed after the final END in the CREATE PROCEDURE statement. When IBConsole encounters this caret it processes the entire CREATE PROCDURE statement. The final SET TERM statement changes the terminator back to a semicolon.

Dropping and Changing Stored Procedures

To remove a stored procedure use the DROP PROCEDURE statement as follows:

DROP PROCEDURE Procedure_Name;

Only SYSDBA or the procedure’s owner can drop a stored procedure. Use the ALTER PROCEDURE statement to change a stored procedure. ALTER PROCEDURE has exactly the same syntax as CREATE PROCEDURE, just change the first word from CREATEto ALTER. At first glance it would appear that you don’t need ALTER PROCEDURE since you could drop the procedure then recreate it to make any changes. However, this will not work if the procedure you are trying to change is called by another procedure. If procedure One calls procedure Two you cannot drop Two because One depends on its existence.

If you use IBConsole to display the metadata for your database and examine the code that creates the stored procedures you will see that InterBase first creates all of the procedures with the body empty as shown in the following example.

CREATE PROCEDURE ADD_EMP_PROJ (
  EMP_NO SMALLINT,
  PROJ_ID CHAR(5)
)
AS
BEGIN EXIT;END ^

CREATE PROCEDURE ALL_LANGS
RETURNS (
  CODE VARCHAR(5),
  GRADE VARCHAR(5),
  COUNTRY VARCHAR(15),
  LANG VARCHAR(15)
)
AS
BEGIN
  EXIT;
END ^

After all of the procedures have been created the script generated by InterBase uses ALTER PROCEDURE to add the body to each stored procedure. For example:

ALTER PROCEDURE ADD_EMP_PROJ(
  EMP_NO SMALLINT,
  PROJ_ID CHAR(5)
)
AS
BEGIN
  BEGIN
    INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
  WHEN SQLCODE -530 DO
    EXCEPTION unknown_emp_id;
  END
  SUSPEND;
END ^

By doing this InterBase eliminates any dependencies between procedures when they are being created. Since the body of every procedure is empty there can be no dependencies where one procedure calls another. When the ALTER PROCEDURE statements are executed they can be run in any order because the shell of any procedure that the one being altered might call already exists.

Calling Select Procedures

Select procedures assign values to output parameters and call SUSPEND to return those values. The following is a simple example of a select procedure.

CREATE PROCEDURE CUSTOMER_COUNT
RETURNS (
  CUSTOMERCOUNT INTEGER
)
AS
BEGIN
  SELECT COUNT(*) FROM CUSTOMER INTO :CustomerCount;
  SUSPEND;
END ^

Figure 1 below shows this procedure being called from IB Console using a SELECT statement. Note that one row and one column are returned and the column name is the name of the output parameter.

Figure 1 – Calling a select procedure from IB Console

You can also call a procedure that requires input parameters from IB Console. Figure 2 shows an example of calling the following procedure.

ALTER PROCEDURE ORDER_LIST(
  CUST_NO INTEGER
)
RETURNS (
  PO_NUMBER CHAR(8)
)
AS
BEGIN
  FOR SELECT PO_NUMBER FROM SALES WHERE CUST_NO=:CUST_NO INTO :PO_NUMBER
  DO SUSPEND;
END ^

Figure 2 – Executing a select procedure with input parameters

The input parameter, CUST_NO, is passed to the procedure in parentheses immediately following the procedure name in the SELECT statement. Note also that this SELECT statement includes both a WHERE and an ORDER BY clause. This allows you to call a stored procedure and return a subset of rows and columns ordered as you wish. In fact, you can treat a select procedure exactly like a table in the SELECT statement that calls it and use all of the features of the SQL SELECT statement to control the result that you get.

You use exactly the same syntax to call a select procedure from another stored procedure, a trigger, or your application. The following SQL statement is from the SQL property of the IBQuery component in the sample application that accompanies this paper. The only difference here is that the parameters :CUST_NO and :PO_NUMBER are used to supply the value of the input parameters.

SELECT * FROM ORDER_LIST(:CUST_NO)
WHERE (PO_NUMBER > :PO_NUMBER)
ORDER BY PO_NUMBER DESC

Figure 3 shows the form used to gather the input data from the user and execute the stored procedure.

Figure 3 – The sample app that runs a select procedure

The following code is from the Execute button’s OnClick event handler and shows how the values are assigned to the input parameters before executing the stored procedure.

procedure TProcForm.SelectBtnClick(Sender: TObject);
begin
  with ProcDm.OrderListQry do
  begin
    Params.ParamByName('CUST_NO').Value := CustomerNoEdit.Text;
    Params.ParamByName('PO_NUMBER').Value := OrderNoEdit.Text;
    Open;
  end; //with
end;

Calling Non-Select Procedures

The following stored procedure is an example of a non-select procedure, that is, a procedure that does not return any results. This procedure takes a single input parameter, FACTOR, and adjusts the minimum and maximum salaries in the Job table by that factor.

CREATE PROCEDURE ADJUST_SALARY_RANGE(
  FACTOR FLOAT
)
AS
BEGIN
  UPDATE JOB
  SET MIN_SALARY=MIN_SALARY * :FACTOR, MAX_SALARY=MAX_SALARY * :FACTOR;
END ^

Use the EXECUTE PROCEDURE command to run this stored procedure from a trigger, another stored procedure, or IB Console. For example:

EXECUTE PROCEDURE ADJUST_SALARY_RANGE(1.1);

To run the procedure from your application use an IBStoredProc component and the following code.

with ProcDm.AdjustSalRngProc do
begin
  Params.ParamByName('Factor').Value := StrToFloat(FactorEdit.Text);
  Prepare;
  ExecProc;
end; //with

At design time set the Database property of the IBStoredProc component to the IBDatabase component for the database that contains the stored procedure. Set the StoredProcName property to the name of the stored procedure you want to execute. Use the Params property’s property editor to create any required input parameter objects and assign their data types and default values.

Using CREATE TRIGGER

The following CREATE TRIGGER statement shows all of the elements of the CREATE TRIGGER syntax. The keywords CREATE TRIGGER are followed by the trigger name, the keyword FOR then the name of the table the trigger is attached to. Next comes either ACTIVE or INACTIVE to indicate whether the trigger will function or not. If the trigger is inactive it will not execute. You will see how to activate and deactivate a trigger later in this paper. The next element of the CREATE TRIGGER statement indicates when the trigger will fire. This will be one of the following six conditions.

1. BEFORE UPDATE
2. AFTER UPDATE
3. BEFORE INSERT
4. AFTER INSERT
5. BEFORE DELETE
6. AFTER DELETE

Next comes the optional POSITION keyword followed by an integer. InterBase allows you to attach any number of triggers to the same event. For example, you could have four triggers for the employee table all of which fire after update. This is a great feature because it allows you to modularize your code. However, the order in which the triggers fire may be important. The POSITION keyword lets you control the firing order based on the integer number supplied. In this example the trigger shown below would fire first because its position is zero. If there were three other triggers you might assign them positions of 10, 20 and 30. It is a good idea to leave a gap in the numbering sequence so you can easily insert another trigger at any point in the firing order in the future.

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
  ACTIVE
  AFTER UPDATE
  POSITION 0
AS
BEGIN
  IF (old.salary <> new.salary) THEN
    INSERT INTO salary_history
      (emp_no, change_date, updater_id, old_salary, percent_change)
      VALUES (
        old.emp_no,
        'NOW',
        user,
        old.salary,
        (new.salary - old.salary) * 100 / old.salary);
END^

After the keyword AS comes any local variable declarations using the same DECLARE VARIABLE syntax that was used for a stored procedure. Finally, comes the procedure body enclosed in a BEGIN/END block.

One thing to keep in mind as you begin using triggers is that a single change to a record in a database can cause many triggers to fire. A change to table A can fire a trigger that updates table B. The update to table B can fire a trigger that inserts a record in table C. Inserting a new record in table C can fire a trigger that updates table D and so on. The second important point about triggers is that a trigger is part of the transaction that caused it to fire. This means that if you start a transaction and update a row which causes a trigger to fire and that trigger changes a table which causes another trigger to fire which updates another table and you then rollback the transaction your change and all of the changes made by all of the triggers fired by your change will be rolled back.

Before or After?

A trigger should fire before the row is updated if you want to change the value of one or more of the columns in the row being updated or if you may want to block the change to the row by raising an exception. For example, you might use a before delete trigger to prevent the user from deleting a customer record if the customer has placed an order within the last two years.

After triggers are used when you want to ensure that the row action as been successfully completed before you take any other action. The trigger above is a good example. This trigger inserts a row into the salary_history table any time an employee’s salary is changed. The history row contains both the old salary and the percent change. Since the update to the employee record could fail for many reasons, such as a value in a field that violates a constraint, you do not want to create the history record until after the update has successfully completed.

Using Old and New

In the sample trigger above you see column names preceded by the “old” and “new” modifiers. In the body of the trigger InterBase makes both the old and new value of each column available, for example old.salary and new.salary. Using the old and new values you can easily create history records, calculate the amount or percentage of change in a numeric value, find records in another table that match either the old or new value or do anything else you can think of.

Raising Exceptions

In a BEFORE trigger you can prevent the row update that fired the trigger from taking place by raising an exception. Before you can raise an exception you must create the exception using the CREATE EXCEPTION statement. For example,

CREATE EXCEPTION CUSTOMER_STILL_CURRENT
“This customer has ordered within the last two years.”

where the keywords CREATE EXCEPTION are followed by the exception’s name and the text of the error message for the exception. To raise this exception in a trigger or stored procedure use the EXCEPTION keyword as shown below.

EXCEPTION CUSTOMER_STILL_CURRENT;

When you raise an exception execution of the trigger or stored procedure terminates. Any statements in the trigger or stored procedure that follow the EXCEPTION statement are not executed. In the case of a BEFORE trigger the update that fired the trigger is aborted. Finally, the exception’s error message is returned to the application. You can drop an exception using the DROP EXCEPTION statement and alter the message associated with an exception using the ALTER EXCEPTION STATEMENT. For example:

ALTER EXCEPTION CUSTOMER_STILL_CURRENT “This customer is still active.”;
DROP EXCEPTION CUSTOMER_STILL_CURRENT;

Using Generators

InterBase does not have an autoincrementing field type. Instead it has a more flexible tool called a generator. A generator returns an incrementing value every time you call it. To create a generator use CREATE GENERATOR as follows:

CREATE GENERATOR CUSTOMER_ID;

Note that there is no DROP GENERATOR statement. Once you create a generator it is part of the database forever. This is not really a problem since a generator is just a single four byte entry in one of the system tables. To get the next value from a generator use the GEN_ID function. For example:

GEN_ID(CUSTOMER_ID, 1);

The first parameter is the name of the generator and the second is the increment. In the example above the value returned will be one greater than the last value. The increment can be set to any value including zero, which is very handy for obtaining the current value of the generator without changing it. You can also change the current value of a generator any time you wish using the SET GENERATOR statement as follows:

SET GENERATOR CUSTOMER_ID TO 1000;

Note that a generator will not be rolled back if you call GEN_ID from within a transaction and then rollback the transaction. Generators are frequently used in triggers to provide a unique surrogate primary key value as in the following example.

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
  ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  new.emp_no=gen_id(emp_no_gen, 1);
END ^

You can also call GEN_ID in a stored procedure that returns the value from the generator in an output parameter to the client application. The client can then assign the value to the primary key and display it to the user when the client creates a new record but before it has been posted. In this case you might also want to include a trigger, like the one above, in case a record is inserted by some client that does not provide the primary key value. All you need to do is alter the code to:

IF new.emp_no IS NULL THEN new.emp_no = gen_id(emp_no_gen, 1);

Now the trigger will supply a value for the key field only if it is null.

Changing and Deleting Triggers

You can use the ALTER TRIGGER statement to alter either the header information or the body of the trigger. The most common use of altering the header information is to activate or deactivate a trigger. Another use is to change the position of the trigger. The following statement will inactivate the trigger.

ALTER TRIGGER SET_EMP_NO INACTIVE;

To alter just the body of the trigger supply its name with no other header information and the new body as shown below. You can also change the header and body at the same time.

ALTER TRIGGER SET_EMP_NO
AS
BEGIN
  IF new.emp_no IS NULL THEN new.emp_no = gen_id(emp_no_gen, 1);
END ^

To drop a trigger use DROP TRIGGER. For example:

DROP TRIGGER SET_EMP_NO;