SQL Triggers

Triggers are actions defined on a table that you can use to enforce consistency rules for a database. They are dictionary objects that identify the appropriate action for the DBMS to perform when a user executes a SQL data modification statement on that table.

To declare a trigger, use the CREATE TRIGGER statement.

CREATE TRIGGER CheckCourseLimit;

The maximum size for a trigger name is 30 characters.

To delete a trigger, use the DROP TRIGGER statement.

DROP TRIGGER CheckCourseLimit;

You cannot invoke a trigger directly; they are invoked as a consequence of an INSERT, UPDATE, or DELETE action on a table with an associated trigger. For more information about the syntax of these statements, refer to the following topics in SQL Engine Reference:

•

•

•

•

•

Note: In order to prevent circumvention of triggers, Pervasive PSQL stamps the data file containing a trigger as a bound data file; this restricts access to Btrieve users and prevents the Btrieve user from performing an action that would fire the trigger in a Pervasive PSQL database. For more information, refer to SQL Engine Reference.

Timing and Ordering of Triggers

Since triggers execute automatically for a given event, it is important to be able to specify when and in what order the trigger or triggers should execute. You specify time and order when you create the trigger.

Specifying the Triggered Action Time

When an event that is associated with a trigger occurs, the trigger must execute either before the event or after the event. For example, if an INSERT statement invokes a trigger, the trigger must execute either before the INSERT statement executes or after the INSERT statement executes.

CREATE TABLE Tuitionidtable (primary key(id), id ubigint)#

CREATE TRIGGER InsTrig

BEFORE INSERT ON Tuition

REFERENCING NEW AS Indata

FOR EACH ROW

INSERT INTO Tuitionidtable VALUES(Indata.ID);

You must specify either BEFORE or AFTER as the triggered action time. The triggered action executes once for each row. If you specify BEFORE, the trigger executes before the row operation; if you specify AFTER, the trigger executes after the row operation.

Note: Pervasive PSQL does not invoke a trigger by enforcing an RI constraint. Also, a table may not have a DELETE trigger defined if an RI constraint may also cause the system to perform cascaded deletes on that table.

Specifying Trigger Order

You may have situations in which an event invokes more than one trigger for the same specified time. For example, an INSERT statement may invoke two or more triggers that are defined to execute after the INSERT statement executes. Since these triggers cannot execute simultaneously, you must specify an order of execution for them.

Since the following CREATE TRIGGER statement specifies an order of 1, any subsequent BEFORE INSERT triggers that you define for the table must have a unique order number greater than 1.

CREATE TRIGGER CheckCourseLimit
BEFORE INSERT
ON Enrolls
ORDER 1

You designate the order value with an unsigned integer, which must be unique for that table, time, and event. If you anticipate inserting new triggers within the current order, leave gaps in the numbering to accommodate this.

If you do not designate an order for a trigger, then the trigger is created with a unique order value that is higher than that of any trigger currently defined for that table, time, and event.

Defining the Trigger Action

The trigger action executes once for each row. The syntax for the trigger action is as follows:

CREATE TRIGGER InsTrig

BEFORE INSERT ON Tuition

REFERENCING NEW AS Indata

FOR EACH ROW

INSERT INTO Tuitionidtable VALUES(Indata.ID);

If the triggered action contains a WHEN clause, then the triggered SQL statement executes if the Boolean expression is true. If the expression is not true, then the triggered SQL statement does not execute. If no WHEN clause is present, then the triggered SQL statement executes unconditionally.

The triggered SQL statement can be either a single SQL statement, including a stored procedure call (CALL procedure_name), or a compound statement (BEGIN...END).

Note: The triggered action must not change the subject table of the trigger.

When you need to reference a column of the old row image (in the case of DELETE or UPDATE) or a column of the new row image (in the case of INSERT or UPDATE) in the triggered action, you must add a REFERENCING clause to the trigger declaration, as follows:

REFERENCING NEW AS N

The REFERENCING clause allows you to maintain information about the data that the trigger modifies.