Background documentationDatabase Triggers Locate this document in the navigation structure

 

Database triggers are programs that implicitly start for a base table when an INSERT-, UPDATE- or DELETE statement is executed. A trigger can contain several SQL statements. A series of control structures are also available for the application programmer. You can program loops or branches, for example, within a trigger.

If, for example, you define for a base table a trigger that is triggered each time an INSERT statement is executed, the actions defined within the trigger are automatically executed each time a new entry is inserted in this table. You can make the execution of a trigger dependent on particular preconditions. You can also formulate UPDATE triggers for changing individual columns only.

You can use triggers to check that values are suitable before entering them in a table or to save values before changing them, for example. When programming a trigger, you can therefore access both the new and the old column values.

Once you have defined a trigger, you have to assign it to a base table or individual columns and to an action such as INSERT, UPDATE, DELETE.

To define a trigger for tables, you have to be the owner of the table in question and have the relevant authorization for carrying out the activities defined within the trigger.

If execution of a trigger fails, you can use ROLLBACK to reset the associated INSERT, UPDATE or DELETE statement.

A trigger can call other triggers implicitly and database procedures explicitly.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating a Database Trigger

To create a database trigger, use the CREATE TRIGGER statement.

CREATE TRIGGER hotel_update FOR hotel.hotel AFTER UPDATE EXECUTE

(TRY

IF NEW.hno <> OLD.hno

THEN UPDATE hotel.room SET hno = :NEW.hno WHERE hno = :OLD.hno;

CATCH

IF $rc <> 100

THEN STOP ($rc, 'unexpected error');)

The trigger ensures that the hotel number in the room table is also changed when a hotel number is changed in the hotel table.

Explanation

  • The trigger event UPDATE is specified after the keyword AFTER. The UPDATE event causes the trigger to be executed for each change made to a row in the hotel table.

  • The SQL statements and control structures (in this example, IF statements) that are required for executing the desired trigger are defined after the keyword EXECUTE.

  • Error Handling: If an SQL error occurs in the statement list between TRY and CATCH, the system branches directly to the statement that follows CATCH. The actual troubleshooting routine can be programmed in this statement.

  • You can use the new hotel number NEW.hno and the old hotel number OLD.hno.

  • The $rc variable returns a numeric error code after a UPDATE statement has been executed. The value 0 means that the statement was successfully executed.

  • An IF statement first evaluates the search condition. If this is met, the statement specified in the THEN branch is executed. Otherwise, the statement in the ELSE branch (if defined) is executed.

See also: SQL Reference Manual, CREATE TRIGGER Statement (create_system_trigger_statement), Routine (routine)

Evaluating System Tables, TRIGGERS

Executing a Database Trigger

The database trigger hotel_update is automatically started as soon as changes are made to the hotel number in the hotel table, using an UPDATE statement.

Deleting a Database Trigger

To delete a database trigger, use the DROP TRIGGER statement.

DROP TRIGGER hotel_update OF hotel.hotel

You can use this SQL statement to delete the trigger definition. This does not affect the table contents.

See also: SQL Reference Manual, DROP TRIGGER Statement (drop_trigger_statement)

More Information

More examples for Data Definition