Example: Audit Log for Large Pay Raises - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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