InterBase Quick Start: Part V - Triggers and Stored Procedures

From InterBase

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

Triggers and stored procedures are part of the metadata of a database. You write them in stored procedure and trigger language, an InterBase extension to SQL. Procedure and trigger language includes SQL DML statements and some powerful extensions, such as:

  • IF … THEN … ELSE
  • WHILE … DO
  • FOR SELECT … DO
  • Exceptions
  • Error handling

A stored procedure is a program that applications (including IBConsole) can call. You can call a stored procedure directly from applications, or you can substitute it for a table or view in a SELECT statement. A stored procedure can receive input parameters from and return values to the calling application.

See the Data Definition Guide for a detailed explanation about stored procedures and triggers.

Triggers

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. Applications or users never call a trigger directly. Instead, when an application or user attempts to perform an action specified in the trigger definition, the trigger automatically executes (fires). Triggers have a great variety of uses, but generally, they allow you to automate tasks.

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

  • set_emp_no: Generates and inserts unique employee number when a row is inserted into the Employee table.
  • set_cust_no: Generates and inserts unique customer number when a row is inserted into the Customer table.
  • save_salary_change: Maintains a record of changes to the salary of employees.
  • new_order: Posts an event when a new row is inserted into the Sales table.

Using SET TERM

You must precede the trigger statement by a SET TERM statement that defines the characters that terminate the trigger statement, because 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.

If you change the terminator using the SET TERM statement, you should change it back after the trigger code.

The following example changes the terminator to !!: 

SET TERM !!;
Note: This statement terminates with a semicolon, because the new terminator comes into effect only after this statement is processed.

Changing the Terminator in the Interactive SQL Window

To change the terminator in the Interactive SQL Window, select Edit > Preferences. Change the value of the Terminator option.

The Structure of Triggers

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 data_type;]
BEGIN
  /* 
   * statements in InterBase
   * procedure and trigger language
   */
END

See CREATE TRIGGER for more information about triggers.

To see some examples of triggers, open the TRIGGERS.SQL script.

Generators: Generating Unique Column Values

Many times 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, in order to increment that number by one and create the new employee number.

Triggers provide a way to automate this process, by using a database object called a generator. A generator is a named variable that you call and increment using the gen_id() function. You initialize the value of the generator with SET GENERATOR. After initialization, each call to gen_id() automatically increments the value of the generator variable. The gen_id() function accepts a generator name and an increment as inputs.

Context Variables

Context variables are unique to triggers. You often use triggers to change a value, and in the process of doing that, you have to temporarily store both the old and new values. The context variables, Old and New, allow you to do this. For more information about context variables, see Data Definition Guide.

Advance To:

Creating a Generator

Retrieved from "http://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=InterBase_Quick_Start:_Part_V_-_Triggers_and_Stored_Procedures&oldid=23846"