15.10 - Example: Audit Log for Large Pay Raises - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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';