InterBase Quick Start: Part V - Creating a Trigger to Maintain Change Records
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.
Creating 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.