Database Triggers 

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, or 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 require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

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.

●     Troubleshooting: 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 the 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)

SQL Reference Manual, 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

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 examples for Data Definition