InterBase Quick Start: Part V - Creating a Trigger to Maintain Change Records
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
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
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
To test this trigger, update an employee record and change the salary.
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
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.
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.