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.

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.

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.

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;

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

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.

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.

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 ^

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 CREATE  to 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.

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;

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);

    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.

In InterBase 6.5 you can easily get the top N or bottom N rows using the ROWS clause. The ROWS clause is an extension to standard SQL that lets you control the number of rows returned by SELECT or the number of rows affected by UPDATE and DELETE. The general syntax is:

ROWS <lower_value> [TO <upper_value>] [ BY <step_value>] [PERCENT] [WITH TIES]

The location of the ROWS clause in the hierarchy of clauses in a SQL statement is shown below.

<from_clause>

  <where_clause>

    <group_by_clause>

      <having_clause>

        <order_by_clause>

          <rows_clause>

The best way to understand what the ROWS clause can do for you is to look at some examples. All of the examples in this article use the sample Employee database that comes with InterBase. The first example shows the use of ROWS in conjunction with the ORDER BY clause to select the five records with the largest discount.

SELECT * FROM SALES

ORDER BY DISCOUNT DESC

ROWS 1 TO 5

The ORDER BY clause sorts the returned records in descending order by DISCOUNT and the ROWS clause limits the number of rows returned to the first five. Although you can use ROWS without ORDER BY it would be unusual to do so since limiting the number of rows infers that you want the first N rows based on some order. As long as the first row you want returned is the first row in the result set you can shorten the query to

SELECT * FROM SALES

ORDER BY DISCOUNT DESC

ROWS 5

If you want all of the rows that have the same values in the fields in the ORDER BY clause as the first five rows use the WITH TIES option shown below.

SELECT * FROM SALES

ORDER BY DISCOUNT DESC

ROWS 1 TO 5 WITH TIES

This query returns eight rows instead of five because some of the values that appear in the DISCOUNT field in the first five rows also appear in other rows. Note that you must use an ORDER BY clause when you use WITH TIES.

Instead of a fixed number of rows you can also request a percentage of the rows in the result set as shown in the following query which returns the top 10% of the rows in SALES by TOTAL_VALUE.

SELECT * FROM SALES

ORDER BY TOTAL_VALUE DESC

ROWS 10 PERCENT

Another interesting use of the PERCENT option is to select an evenly distributed subset of rows. This query returns five rows with each of those rows separated from its neighbor by 20% of the rows in the table. Although this query does not use an ORDER BY clause it could. For example, to obtain an even distribution of rows geographically you could order the table by state or zip code.

SELECT * FROM CUSTOMER

ROWS 100 BY 20 PERCENT

You can also use the ROWS clause in conjunction with aggregates.  The following query returns the top three sales reps based on the total value of their orders while the second query returns the top 25% of sales reps based on the total value of their orders.

SELECT SALES_REP, SUM(TOTAL_VALUE) AS TOTAL
FROM SALES
GROUP BY SALES_REP
ORDER BY 2 DESC
ROWS 3

SELECT SALES_REP, SUM(TOTAL_VALUE) AS TOTAL
FROM SALES
GROUP BY SALES_REP
ORDER BY 2 DESC
ROWS 25 PERCENT

The ROWS clause can also be used with UPDATE to give the ten highest paid employees a raise.

UPDATE EMPLOYEE

SET SALARY = SALARY * 1.01

ORDER BY SALARY DESC

ROWS 10

You can also use ROWS with DELETE to delete the ten highest paid employees. If you want to delete everyone whose salary is equal to one of the ten highest paid employees just add the WITH TIES option.

DELETE FROM EMPLOYEE

ORDER BY SALARY DESC

ROWS 10

For earlier versions of InterBase you can easily get the top N or bottom N rows using stored procedures. The following stored procedure returns the employees with the top salaries. The Top_Salaries stored procedure takes a single parameter which is the number of records to return. A local integer variable, I, is used to count the number of records returned. The FOR SELECT statement gets all of the employee records sorted in descending order by salary. The statements in the DO clause increment the counter variable I and check to see if I is greater than the requested number of records. If so the call to EXIT causes the procedure to end. If this record is to be returned the SUSPEND command pauses the procedure until the record is fetched by the client.

CREATE PROCEDURE TOP_SALARIES 

(

  NUM_RECORDS INTEGER

)

RETURNS

(

  LAST_NAME VARCHAR(20),

  FIRST_NAME VARCHAR(15),

  DEPT_NO CHAR(3),

  SALARY NUMERIC(15, 2)

)

AS

DECLARE VARIABLE I INT;

BEGIN

  I = 0;

  FOR SELECT LAST_NAME, FIRST_NAME, DEPT_NO, SALARY

      FROM EMPLOYEE ORDER BY SALARY DESC

      INTO :LAST_NAME, :FIRST_NAME, :DEPT_NO, :SALARY

  DO BEGIN

    I = I + 1;

    IF (I > NUM_RECORDS) THEN EXIT;

    SUSPEND;

  END

END

Getting the bottom N records is done in exactly the same way except the FOR SELECT statement sorts the records in ascending order instead of descending order. The sample application uses an IBQuery component with the following SQL statement to call the stored procedure.

procedure TProcForm.TopBtnClick(Sender: TObject);

begin

  with ProcDm.TopQry do

  begin

    SQL.Clear;

    SQL.Add('SELECT * FROM TOP_SALARIES(' + NumEdit.Text + ')');

    Open;

  end; //with

end;

Although the Interbase manuals say that you cannot use BLOBs in stored procedures this is not true. The following select procedure returns records that include a BLOB field.

CREATE PROCEDURE GET_JOBS 

RETURNS

(

  JOB_CODE VARCHAR(5),

  JOB_GRADE SMALLINT,

  JOB_COUNTRY VARCHAR(15),

  JOB_TITLE VARCHAR(25),

  MIN_SALARY NUMERIC(15, 2),

  MAX_SALARY NUMERIC(15, 2),

  JOB_REQUIREMENT BLOB

)

AS

 begin

for select JOB_CODE, JOB_GRADE, JOB_COUNTRY, JOB_TITLE,

           MIN_SALARY, MAX_SALARY, JOB_REQUIREMENT

           from JOB into :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :JOB_TITLE,

           :MIN_SALARY, :MAX_SALARY, :JOB_REQUIREMENT do

   suspend;

end

The following procedure lets you insert a new record that includes a BLOB field.

CREATE PROCEDURE ADD_JOB 

(

  JOB_CODE VARCHAR(5),

  JOB_GRADE SMALLINT,

  JOB_COUNTRY VARCHAR(15),

  JOB_TITLE VARCHAR(25),

  MIN_SALARY NUMERIC(15, 2),

  MAX_SALARY NUMERIC(15, 2),

  JOB_REQUIREMENT BLOB

)

AS

 begin

   insert into JOB(JOB_CODE, JOB_GRADE, JOB_COUNTRY, JOB_TITLE,

                   MIN_SALARY, MAX_SALARY, JOB_REQUIREMENT)

          values(:JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :JOB_TITLE,

                 :MIN_SALARY, :MAX_SALARY, :JOB_REQUIREMENT);

end

The following code is from the sample application�s Add Job button�s OnClick event handler. An IBStoredProc component is used to run the stored procedure. The code below assigns values to each of the input parameters then calls the IBStoredProc�s ExecProc method to call the stored procedure.

procedure TProcForm.AddJobBtnClick(Sender: TObject);

begin

  with ProcDm.AddJobProc do

  begin

    ParamByName('Job_Code').AsString := JobCodeEdit.Text;

    ParamByName('Job_Grade').AsInteger := StrToInt(JobGradeEdit.Text);

    ParamByName('Job_Country').AsString := JobCountryEdit.Text;

    ParamByName('Job_Title').AsString := JobTitleEdit.Text;

    ParamByName('Min_Salary').AsFloat := StrToFloat(MinSalaryEdit.Text);

    ParamByName('Max_Salary').AsFloat := StrToFloat(MaxSalaryEdit.Text);

    ParamByName('Job_Requirement').AsString := JobReqMemo.Text;

    ExecProc;

  end; //with

end;

However, there is no way to manipulate the BLOB field value within a stored procedure.

CREATE PROCEDURE TrimRight (str VARCHAR(10))

  RETURNS (ret VARCHAR(10)) AS

BEGIN

  ret = str;

  IF (str IS NULL) THEN BEGIN SUSPEND; EXIT; END

  IF (str = '')    THEN BEGIN ret = ''; SUSPEND; EXIT; END

  BEGIN

    ret = CAST (str AS char(9));

    ret = CAST (str AS char(8));

    ret = CAST (str AS char(7));

    ret = CAST (str AS char(6));

    ret = CAST (str AS char(5));

    ret = CAST (str AS char(4));

    ret = CAST (str AS char(3));

    ret = CAST (str AS char(2));

    ret = CAST (str AS char(1));

    SUSPEND;

    WHEN ANY DO SUSPEND;

  END

END

This procedure works because casting a VARCHAR or CHAR to a smaller size truncates the string if the dropped characters are spaces and will raise an exception if the characters to be dropped are not spaces.

CREATE PROCEDURE Trunc10To5 (a varchar(10))

  RETURNS (ret varchar(5)) AS

BEGIN

  ret = '';

  ret = a;

  WHEN ANY DO EXIT;

END

When you assign a string to a shorter variable the string is truncated and assigned to the variable but an exception is raised. All you have to do is use WHEN ANY DO to ignore the exception. The limitation of this procedure is that the size you are truncating to is fixed.

CREATE PROCEDURE Len (str VARCHAR(100))

  RETURNS (len INTEGER) AS

DECLARE VARIABLE pat VARCHAR(100);

BEGIN

  len = null;

  IF (str IS NULL) THEN EXIT;

  pat = '';

  len = 0;

  WHILE (NOT str LIKE pat) DO BEGIN

    pat = pat || '_';

    len = len + 1;

  END

END

This procedure uses a WHILE loop and the LIKE operator to count the characters in the string.

CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))

  RETURNS (Pos INTEGER) AS

DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */

DECLARE VARIABLE Tmp VARCHAR(100);

BEGIN

  IF (SubStr IS NULL OR Str IS NULL) THEN 

  BEGIN 

    Pos = NULL; 

    EXIT; 

  END

  SubStr2 = SubStr || '%';

  Tmp = '';

  Pos = 1;

  WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN

    SubStr2 = '_' || SubStr2;

    Tmp = Tmp || '_';

    Pos = Pos + 1;

  END

  IF (Str LIKE Tmp) THEN Pos = 0;

END

Here the tmp variable stops the loop when the number of iterations equals the length of the string. This works because when the number of underscore characters in tmp equals the length of the string Str NOT LIKE Tmp will be false. With each iteration through the loop an underscore is prepended to SubStr2 to test a new location in Str.

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,

            CURRENT_DATE,

            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.

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.

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.

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;

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.

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;