Introduction to Interbase Stored Procedures and Triggers

A stored procedure is a program written in Interbases procedure and trigger language that is stored as part of the database. Stored procedures can be called by client applications or by other stored procedures or triggers. Triggers are almost identical to stored procedures with one exception, the way they are called. Triggers are called automatically when a change to a row in a table occurs. This paper examines stored procedures first followed by triggers. As you will see, most of what is said about stored procedures applies to triggers as well.

Advantages of Stored Procedures

The biggest advantage of stored procedures is reduced network traffic. Since stored procedures are executed by Interbase on the database server machine you can use them to move much of your data manipulation code to the server. This eliminates transferring data from the server across the network to the client for manipulation, and reduced network traffic means increased performance, particularly across a WAN or any low speed connection.

Stored procedures improve performance in another way. While you can use queries to do many of the same things that could be done in a stored procedure a query has a significant disadvantage. Each time the client application sends a SQL statement to the database server for execution the SQL statement must be parsed, submitted to the query optimizer and analyzed to formulate an execution plan. Stored procedures are parsed, optimized and stored in executable form when they are added to the database. Since a stored procedure does not have to be parsed and optimized each time it is called it will execute faster than an equivalent query. Stored procedures can also perform much more complex operations than a single query.

If more than one application will access the database, stored procedures will also save maintenance and development time since any application can call a stored procedure. Maintenance is easier because you can change the stored procedure without having to change or even recompile any of the client applications.

Finally, stored procedures are a valuable part of database security since store procedures can access tables the user does not have rights to. For example, suppose a user needs to run reports that show total salary by department and pay grade. While this information must come from the employee salary table you do not want this user to have access to the salary table and the individual salaries of all employees. The solution is to write a stored procedure to calculate the summary information from the salary table and grant the procedure read access to the table. You can then grant the user execute access to the stored procedure. The user does not need any rights to the salary table.

When Should You Use Stored Procedures?

The short answer is, any time you can. There is no disadvantage to using a stored procedure. There are only two limitations. First, you must be able to pass any variable information to the stored procedure as parameters or place the information in a table that the stored procedure can access. Second, the procedure and trigger language may be too limited for complex calculations.

Using Create Procedure

Stored procedures are created using the CREATE PROCEDURE statement that has the following syntax.

CREATE PROCEDURE ProcedureName

        <input parameters>

RETURNS

        


AS

        

BEGIN

        

END

Input parameters provide a way to pass values that are used to modify the procedures behavior from the client application to the procedure. For example, if the purpose of the procedure is to compute the total monthly payroll for a department the department number would be passed to the procedure as an input parameter. Output, or return, parameters provide a way for the procedure to return information to the client applications. In this example the total monthly payroll for the department could be returned in an output parameter. A parameter can have any Interbase data type except BLOB or array. The following procedure shows the use of both input and output parameters.

CREATE PROCEDURE SUB_TOT_BUDGET
(
HEAD_DEPT CHAR(3)
)
RETURNS (
TOT_BUDGET NUMERIC (15, 2),
AVG_BUDGET NUMERIC (15, 2),
MIN_BUDGET NUMERIC (15, 2),
MAX_BUDGET NUMERIC (15, 2)
)
AS
 
BEGIN
 SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
  FROM department
  WHERE head_dept = :head_dept
  INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
 SUSPEND;
END ^

This stored procedure declares one input parameter, HEAD_DEPT whose type is CHAR(3) and four output parameters, TOT_BUDGET, AVG_BUDGET, MIN_BUDGET, and MAX_BUDGET all of type NUMERIC(15, 2). Both the input parameter and output parameter declarations must be enclosed in parentheses. The SUSPEND command pauses the stored procedure until the client fetches the values of the output parameters. Suspend is covered in more detail later in this paper.

Declaring Local Variables

You can declare local variables of any Interbase type within a stored procedure. These variables exist only while the stored procedure is executing and their scope is local to the procedure. Note that there are no global variables available when working with stored procedures and triggers and they are not needed. If you have values that must be shared by two or more procedures either pass them as parameters or store them in a table.

Local variables are declared after the AS keyword and before the BEGIN that identifies the beginning of the body of the procedure. To declare a variable use the DECLARE VARIABLE statement. For example:

DECLARE VARIABLE OrderCount Integer;

DECLARE VARIABLE TotalAmount NUMERIC(15,2);

Note that each DECLARE VARIABLE statement can only declare one variable. The following procedure illustrates the use of the DECLARE VARIABLE statement. It declares four local variables, ord_stat, hold_stat, cust_no and any_po. Notice that when a variable is used in the INTO clause of a SELECT statement that a colon must be added as the first character of the variable name, however, when the variable is used in any other statement the colon is not required.

CREATE PROCEDURE SHIP_ORDER
(
PO_NUM CHAR(8)
)
AS
 DECLARE VARIABLE ord_stat CHAR(7);
 DECLARE VARIABLE hold_stat CHAR(1);
 DECLARE VARIABLE cust_no INTEGER;
 DECLARE VARIABLE any_po CHAR(8);
BEGIN
 SELECT s.order_status, c.on_hold, c.cust_no
 FROM sales s, customer c
 WHERE po_number = :po_num
 AND s.cust_no = c.cust_no
 INTO :ord_stat, :hold_stat, :cust_no;
 
 /* This purchase order has been already shipped. */
 IF (ord_stat = 'shipped') THEN
 BEGIN
  EXCEPTION order_already_shipped;
  SUSPEND;
 END
 
 /* Customer is on hold. */
 ELSE IF (hold_stat = '*') THEN
 BEGIN
  EXCEPTION customer_on_hold;
  SUSPEND;
 END
 
 /*
  * If there is an unpaid balance on orders shipped over 2 months ago,
  * put the customer on hold.
  */
 FOR SELECT po_number
  FROM sales
  WHERE cust_no = :cust_no
  AND order_status = 'shipped'
  AND paid = 'n'
  AND ship_date < CAST('NOW' AS DATE) - 60
  INTO :any_po
 DO
 BEGIN
  EXCEPTION customer_check;
 
  UPDATE customer
  SET on_hold = '*'
  WHERE cust_no = :cust_no;
 
  SUSPEND;
 END
 
 /*
  * Ship the order.
  */
 UPDATE sales
 SET order_status = 'shipped', ship_date = 'NOW'
 WHERE po_number = :po_num;
 
 SUSPEND;
END ^

Writing the Body of the Procedure

The body of a stored procedure consists of a compound statement. A compound statement is any number of Interbase procedure and trigger language statements enclosed in a BEGIN/END block. The body of the following procedure consists of a SELECT statement and a SUSPEND between the BEGIN and END keywords.

CREATE PROCEDURE SUB_TOT_BUDGET
(
HEAD_DEPT CHAR(3)
)
RETURNS (
TOT_BUDGET NUMERIC (15, 2),
AVG_BUDGET NUMERIC (15, 2),
MIN_BUDGET NUMERIC (15, 2),
MAX_BUDGET NUMERIC (15, 2)
)
AS
 
BEGIN
 SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
  FROM department
  WHERE head_dept = :head_dept
  INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
 SUSPEND;

END ^

Each statement in the body of the procedure must end with a semicolon.

Other Language Elements

The Interbase procedure and trigger language includes all of the constructs of a basic structured programming language as well as statements unique to working with data in tables. The following sections describe these elements.

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.

IfThenElse

The syntax of the Interbase IF statement is:

IF  THEN

        

ELSE

        

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.

Conditional Expression	Description
value BETWEEN value AND value	An inclusive range
value LIKE value	Value on right includes one or more wildcards. Use % for 0 or more characters and _ for one character.
value IN (value1, value2, value3, )	Member of a list of values.
value EXISTS (subquery)	True if the value matches one of the values returned by the subquery.
value ANY (subquery)	True if the value matches any of the rows returned by the subquery.
value ALL (subquery)	True if the value matches all of the rows returned by the subquery.
value IS NULL	True if value is null.
value IS NOT NULL	True if value is not null.
value CONTAINING value	Case insensitive substring search.
value STARTING WITH value	Case sensitive match at beginning of string.

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;

WhileDo

The WHILEDO 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

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 SELECTDO

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 SELECTDO 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 ^

Creating and Modifying Stored Procedures

The normal method of creating databases and database objects with Interbase is to create a SQL script in IBConsole or a text editor and then use IBConsole to execute the script. This creates a problem since both IBConsole and the stored procedure use a semicolon to terminate statements.

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 procedures 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 CREATE to ALTER. At first glance it would appear that you dont 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 Stored Procedures

You can call stored procedures from other stored procedures or triggers, from IB Console and from your applications. Interbase stored procedures are divided into two groups with respect to how they are called. Procedures that return result values through output parameters are called select procedures because they can be used in place of a table name in a SQL SELECT statement.

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 buttons 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 propertys property editor to create any required input parameter objects and assign their data types and default values.

Triggers

Triggers are identical to stored procedures with the following exceptions.

1. Triggers are called automatically when the data in the table the trigger is attached to changes.

2. Triggers take no input parameters.

3. Triggers do not return values

4. Triggers are created by the CREATE TRIGGER statement.

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 ^

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 employees 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 exceptions 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 exceptions 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;


Summary

Stored procedures and triggers are at the heart of developing client/server applications. Using stored procedures and triggers you can:
	

Reduce network traffic.


Create a common set of business rules in the database that 
	apply to all client applications.


Provide common routines that are available to all client 
	applications thereby reducing application development and maintenance time.


Centralize processing on the server and reduce the hardware 
	requirements of the client.


Improve application performance.



For more information on Interbase stored procedures and triggers see the Data Definition Guide and the Language Guide as well as the stored procedures and triggers in the sample EMPLOYEE.GDB database.