Triggers are particularly useful for audits of all kinds. This example shows an AFTER row trigger that inserts a log record whenever an employee gets a raise larger than ten percent.
These are the table definitions:
CREATE TABLE employee ( name CHARACTER(30), dept_Id INTEGER, salary DECIMAL(10,2), comments CHARACTER(30)); CREATE TABLE salary_log ( user_name CHARACTER(30), emp_name CHARACTER(30), old_salary DECIMAL(10,2), new_salary DECIMAL(10,2));
Now define the trigger on the employee table:
CREATE TRIGGER RaiseTrig
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS OldRow NEW AS NewRow
FOR EACH ROW
WHEN ((NewRow.salary-OldRow.salary)/OldRow.salary >.10)
INSERT INTO salary_log
VALUES ('USER', NewRow.name, OldRow.salary, NewRow.salary);
When the following requests are processed, two inserts are made to salary_log. The third update does not meet the WHEN condition of the trigger, so no corresponding row is inserted in salary_log.
UPDATE employee SET salary = salary*1.5, comments = 'Employee of the Year' WHERE name = 'John Smith'; UPDATE employee SET salary = salary*2, comments = 'Employee of the Decade' WHERE name = 'Min Chan'; UPDATE employee SET salary = salary*1.05, comments = 'Normal midrange raise' WHERE name = 'Lev Ulyanov';