InterBase Quick Start: Part V - Creating a Trigger to Maintain Change Records

From InterBase

Go Up to InterBase Quick Start: Part V - Advanced Topics

Enter the following CREATE TRIGGER statement to create the save_salary_change trigger, which maintains a record of changes to the salaries of employees in the Salary_history table:

CREATE TRIGGER save_salary_change FOR employee
AFTER UPDATE AS
BEGIN
  IF (OLD.salary <> NEW.salary) THEN
    INSERT INTO salary_history
                (emp_no,
                change_date,
                updater_id,
                old_salary,
                percent_change)
    VALUES
                (OLD.emp_no,
                'NOW',
                USER,
                OLD.salary,
                (NEW.salary - OLD.salary) * 100 / OLD.salary);
END

This trigger fires AFTER UPDATE of the Employee table. It compares the value of the salary column before the update to the value after the update and if they are different, it enters a record in Salary_history that consists of the employee number, date, previous salary, and percentage change in the salary.

The values that are entered to the Salary_history table and are taken from the Employee table, are always preceded by the Old or New context variable. That is because InterBase creates two versions of a record during the update process, and you must specify which version the value comes from.

Additionally, this example makes use of two other InterBase features: it inserts the current date into a column of DATE data type by supplying the string 'NOW' in single quotes, and it inserts the name of the user that is currently connected to the database by supplying the keyword USER.

To test this trigger, update an employee record and change the salary.

Image 025.jpgCreating a Trigger that Posts an Event

Execute the following CREATE TRIGGER statement to create a trigger post_new_order that posts an event named new_order whenever a record is inserted into the Sales table.

CREATE TRIGGER post_new_order FOR sales
AFTER INSERT AS
BEGIN
  post_event 'new_order';
END

An event is a message that a trigger or stored procedure passes to the InterBase event manager to notify interested applications of the occurrence of a particular condition. Applications that register interest in an event can pause execution and wait for the specified event to occur.

The post_new_order trigger fires after a new record is inserted into the Sales table. When this event occurs, interested applications can take action, such as printing an invoice or notifying the shipping department.

Advance To: