InterBase Quick Start: Part V - Triggers and Stored Procedures

From InterBase
Jump to: navigation, search

Go Up to InterBase Quick Start: Part V - Advanced Topics

Stored procedures and triggers are part of a metadata of a database and are written in stored procedure and trigger language, an InterBase extension to SQL. Procedure and trigger language includes SQL data manipulation statements and some powerful extensions, including IF … THEN … ELSE, WHILE … DO, FOR SELECT … DO, exceptions, and error handling.

  • A stored procedure is a program that can be called by applications or from IBConsole.

Stored procedures can be invoked directly from applications, or can be substituted for a table or view in a SELECT statement. They can receive input parameters from and return values to the calling application.

  • A trigger is a self-contained routine that is associated with a table. A trigger definition specifies an action to perform when a specified event, such as an update, insert, or delete occurs on the table. A trigger is never called directly by an application or user. Instead, when an application or user attempts to perform the action stated in the trigger definition, the trigger automatically executes, or fires.

See the Data Definition Guide for a full explanation of stored procedures and triggers.

Triggers

Triggers have a great variety of uses, but in general, they permit you to automate tasks that would otherwise have to be done manually. You can use them to define actions that should occur automatically when data is inserted, updated, or deleted in a particular table. Triggers are a versatile tool, with a wide range of uses.

The triggers defined in the TUTORIAL database perform the following actions:

  • The set_emp_no trigger generates and inserts unique employee numbers when a row is inserted into the Employee table and the set_cust_no trigger does the same for customer numbers in the Customer table.
  • The save_salary_change trigger maintains a record of employees’ salary changes.
  • The new_order trigger posts an event when a new row is inserted into the Sales table.

Using SET TERM

In command-line isql and SQL scripts, the trigger statement must be preceded by a SET TERM statement that defines what characters will terminate the trigger statement, since the SQL statements in the body of a trigger must each end with a semicolon (;). The double exclamation mark (!!) is a common choice for this terminator.

In environments where the terminator was changed using the SET TERM statement, the trigger code should be followed by another SET TERM statement to change the terminator back to a semicolon.

The following example changes the terminator to !!, but ends with the current terminator (semicolon), because that is the one in effect for this statement:

SET TERM !!;

Changing the Terminator in IBConsole

To change the terminator in IBConsole, choose Edit > Preferences and click the Interactive SQL tab. Enter a double exclamation mark (!!), or another terminator if you prefer, in the Terminator field.

The Structure of Triggers

  • The CREATE TRIGGER keywords are followed by the trigger name and the table for which the trigger is defined.
  • The next line determines when the trigger fires. Choices are before or after an insert, deletion, or update. If more than one trigger is defined for a particular point – such as AFTER INSERT – you can add a position number that specifies the sequence in which the trigger should fire.
  • If the trigger uses local variables, they are declared next.
  • The variables, if any, are followed by SQL statements that determine the behavior of the trigger. These statements are bracketed between the keywords BEGIN and END. Each of these SQL statements ends with a semicolon.
  • If you are working in isql or writing a script, the END keyword is followed by the terminator that was defined by the SET TERM statement.

The syntax for a trigger looks like this.

CREATE TRIGGER trigger_name FOR Table_name 
 {BEFORE | AFTER} {INSERT | DELETE | UPDATE} [POSITION NUMBER] 
 AS 
 [DECLARE VARIABLE variable_name datatype;] 
 BEGIN
 statements in InterBase procedure and trigger language 
 END

See Triggers.sql for an example. The Language Reference Guide and Data Definition Guide all contain more information on triggers.

Generators: Generating Unique Column Values

There are many cases in which table columns require unique, sequential values. The emp_no column of the Employee table is a good example. Without a trigger, you would have to know what the last employee number is each time you add a row for a new employee, so that you could increment it by one to create the new employee number. This is cumbersome and error prone.

Triggers provide a way to automate this process, by using a handy database object called a generator. A generator is a named variable that is called and incremented through the gen_id( ) function. The value of the generator is initialized with SET GENERATOR. After that, it generates the next incremental value each time gen_id( ) is called. The gen_id() function takes a generator name and an increment as inputs.

Context Variables

Context variables are unique to triggers. Triggers are often used to change a value, and in the process of doing so, must temporarily store both the old and new values. The context variables, Old and New, are the mechanisms by which triggers do this. As you perform the exercises in this section, look for them in contexts such as New.emp_no = gen_id(emp_no_gen, 1). For more information about context variables, see Data Definition Guide.

Advance To:

Image 025.jpg Creating a Generator