About Triggers

Go Up to Triggers (Data Definition Guide)

A trigger is a self-contained routine associated with a table or view that automatically performs an action when a row in the table or view is inserted, updated, or deleted.

A trigger is never called directly. Instead, when an application or user attempts to INSERT, UPDATE, or DELETE a row in a table, any triggers associated with that table and operation are automatically executed, or fired.

Triggers can make use of exceptions, named messages called for error handling. When an exception is raised by a trigger, it returns an error message, terminates the trigger, and undoes any changes made by the trigger, unless the exception is handled with a WHEN statement in the trigger.

The advantages of using triggers are:

Automatic enforcement of data restrictions, to make sure users enter only valid values into columns.
Reduced application maintenance, since changes to a trigger are automatically reflected in all applications that use the associated table without the need to recompile and re-link.
Automatic logging of changes to tables. An application can keep a running log of changes with a trigger that fires whenever a table is modified.
Automatic notification of changes to the database with event alerters in triggers.

Working with Triggers

With isql, you can create, alter, and drop triggers and exceptions. Each of these operations is explained in this chapter. There are two ways to create, alter, and drop triggers with isql:

Interactively
With an input file containing data definition statements

It is preferable to use data definition files, because it is easier to modify these files and provide a record of the changes made to the database. For simple changes to existing triggers or exceptions, the interactive interface can be convenient.

Working with Triggers Using a Data Definition File

To create or alter a trigger through a data definition file, follow these steps:

Use a text editor to write the data definition file.
Save the file.
Process the file with isql. Use the command:
```
isql -input filename database_name
```
where <filename> is the name of the data definition file and <database_name> is the name of the database used. Alternatively, from within isql, you can interactively process the file using the command:
```
SQL> input filename;
```

Note:
If you do not specify the database on the command line or interactively, the data definition file must include a statement to create or open a database.

The data definition file may include:

Statements to create, alter, or drop triggers. The file can also include statements to create, alter, or drop procedures and exceptions. Exceptions must be created and committed before they can be referenced in procedures and triggers.
Any other isql statements.

Advance To:

Creating Triggers