19. Triggers

19.1 Overview

Triggers are procedures which are stored in the database and are implicitly executed when an INSERT, UPDATE or DELETE statement is issued against the associated table. Triggers are similar to Persistent Stored Modules, discussed in Chapter C. Triggers can include SQL/92 and SQL/PSM statements to execute as a unit and can invoke stored procedures. However, the main difference between Triggers and stored procedures is the way they are invoked. While a procedure is explicitly executed by a user, application or trigger, one trigger is implicitly executed by Ovrimos when a triggering INSERT, UPDATE or DELETE statement is issued, no matter which user has logged in or which application is being used.

Triggers are often used to do the following:

• cascade changes through related tables in the database, such as deleting a record.
• prevent invalid transactions.
• enforce security authorizations.

19.2 Introducing Triggers

Triggers are defined only on tables, not on views and are stored in the associated database. It is also possible to have cascading triggers when a statement in a trigger body causes another trigger to be activated.

  

Figure D.1: Example of an implicit execution of Triggers

While Triggers are useful for customizing a database, their excessive use can result in complex interdependencies which may be difficult to maintain in a large application.

Ovrimos Triggers consist of two parts: a triggering statement and an action.

Example 1   The following is an example of the CREATE TRIGGER statement:

CREATE TRIGGER ORDER_TRIGGER 
AFTER UPDATE ON ORDERS 
FOR EACH ROW 
BEGIN 
 IF NEW.ORDER_ID <> OLD.ORDER_ID THEN
  UPDATE ORDER_CONS SET ORDER_ID = NEW.ORDER_ID 
  WHERE ORDER_ID = OLD.ORDER_ID;
 END IF;
END