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 employees’ salaries 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.
Notice that when the values to be entered in the Salary_history table are to be taken from the Employee table, they 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 is to come from.
In addition, note that 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 who is currently connected to the database by supplying the keyword USER.
Update an employee record and change the salary to see how this trigger works.
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 passed by a trigger or stored procedure to the InterBase event manager to notify interested applications of the occurrence of a particular condition. Applications that have registered interest in an event can pause execution and wait for the specified event to occur. For more information on events, see the Programmer’s Guide.
The post_new_order trigger fires after a new record is inserted into the Sales table – in other words when a new sale is made. When this event occurs, interested applications can take action, such as printing an invoice or notifying the shipping department.