02 - DB2 for i (EN)02a - SQL (EN)04g - System Admin miscellanea

SQL – Audit trigger … who moved my cheese!

The trigger and test sources of this post are available on our Github GIST:

Have you read Spencer Johnson’s book “Who Moved My Cheese? … if you haven’t, I recommend you do it … it’s a simple parable about change (in your life, in your job or in your family too). In this book, cheese is a metaphor for what you want for life, a good job, a good relationship, money or what else. But things happen, life changes, whether we like or not, and the problem is that no source of cheese lasts forever.

But what about cheese and a SQL Trigger? Nothing!, but I was answering to a customer’s request: “Who changed the method of payment in that order?”, so I answered, “OK, but I want to know who moved my cheese!” … and he, “I don’t care about your cheese! I want to know who changed that order!”.

So I stopped to think about my cheese and opened my ACS SQL Script to create a simple audit trigger on that order table, but one these days I will reread the Spencer Johnson’s book Who Moved My Cheese?, it’s on my Kindle library.

All SQL sources of all this post are available on our Github Gist : if you want to copy and paste some code open the Github Gist link .

Prerequisites for the example: SAMPLEDB

The example shown here is based on the typical EMPLOYEE table of a SAMPLEDB, a library (SQL SCHEMA) with simple examples of related tables


CALL QSYS.CREATE_SQL_SAMPLE (‘SAMPLEDB’)

Step 1, the LOG table

Let’s start creating our Log table … where we are going to save the events and changes made on our main table

CREATE OR REPLACE TABLE sampledb.Employee_AuditLog (
MYKEY VARCHAR (300),
EVENT CHAR (1),
USERNAME CHAR (10),
JOBNAME VARCHAR (50),
DATA_CHANGES VARCHAR (300),
ADDSTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Step 2, let’s create our trigger

We create the trigger on the EMPLOYEE table that monitors the INSERT / DELETE / UPDATE events: For the INSERT and DELETE we will simply log the user and job that made the operation and the reference to our EMPNO, code you depend on. For the UPDATE instead we go in detail to check some specific fields of our table, making the AUDIT file readable by the user:

---------------------------------------------
- Then: Create an AUDIT Trigger on my TABLE
---------------------------------------------
  CREATE OR REPLACE TRIGGER sampledb.Employee_trg
  AFTER INSERT OR DELETE OR UPDATE ON sampledb.Employee
  REFERENCING NEW ROW AS N OLD ROW AS O
  FOR EACH ROW MODE DB2ROW
  BEGIN
  DECLARE V_USERNAME CHAR (10);
  SET V_USERNAME = CURRENT_USER; 
  - WHEN INSERTING A NEW RECORD WILL LOG USER / JOB and
  - JUST A FEW INFO
    IF INSERTING THEN
      INSERT INTO sampledb.Employee_AuditLog
               (mykey, event, username, jobname, data_changes)
               VALUES ('EMPNO:' concat n.empno,
                      'THE',
                      current user,
                      JOB_NAME,
                      'INSERT NEW EMPLOYEE:' 
                         CONCAT trim (N.EMPNO)
                         CONCAT ''
                         CONCAT trim (N.LASTNAME)
                         CONCAT ''
                         CONCAT TRIM (N.FIRSTNME)
                      );
    END IF;
    - WHEN DELETING 
    IF DELETING THEN
      INSERT INTO sampledb.Employee_AuditLog
               (mykey, event, username, jobname, data_changes)
               VALUES ('EMPNO:' concat o.empno,
                      'D',
                      current user,
                      JOB_NAME,
                      'DELETED EMPLOYEE:' 
                         CONCAT trim (O.EMPNO)
                         CONCAT ''
                         CONCAT trim (O.LASTNAME)
                         CONCAT ''
                         CONCAT TRIM (O.FIRSTNME)
                      );
    END IF;

    - WHEN UPDATING a ROW WILL LOG ONLY CHANGES 
    - TO SOME FIELDS
    IF UPDATING THEN
      -------------------------
      - FISTNAME or LASTNAME
      -------------------------
      IF O.FIRSTNME <> N.FIRSTNME OR
         O.LASTNAME <> N.LASTNAME THEN
         INSERT INTO sampledb.Employee_AuditLog
               (mykey, event, username, jobname, data_changes)
               VALUES ('EMPNO:' concat N.empno,
                      'U',
                      current user,
                      JOB_NAME,
                      'NAME CHANGE:' 
                         CONCAT trim (O.LASTNAME)
                         CONCAT ''
                         CONCAT trim (O.FIRSTNME)
                         CONCAT '->'
                         CONCAT trim (N.LASTNAME)
                         CONCAT ''
                         CONCAT TRIM (N.FIRSTNME)
                      );
         
      END IF;   
      -------------------------
      - WORKDEPT and JOB
      -------------------------
       IF O.WORKDEPT <> N.WORKDEPT OR 
          O.JOB <> N.JOB THEN
         INSERT INTO sampledb.Employee_AuditLog
               (mykey, event, username, jobname, data_changes)
               VALUES ('EMPNO:' concat N.empno,
                      'U',
                      current user,
                      JOB_NAME,
                      'WORKDEPT / JOB CHANGE:'
                         CONCAT 'DEPT:' 
                         CONCAT trim (O.WORKDEPT)
                         CONCAT 'JOB:' 
                         CONCAT trim (O.JOB)
                         
                         CONCAT '->'
                         CONCAT 'DEPT:' 
                         CONCAT trim (N.WORKDEPT)
                         CONCAT 'JOB:' 
                         CONCAT trim (O.JOB)

                      );
         
      END IF;   
      -------------------------
      - SALARY OR BONUS
      -------------------------
       IF O.SALARY <> N.SALARY OR
          O.BONUS <> N.BONUS THEN
         INSERT INTO sampledb.Employee_AuditLog
               (mykey, event, username, jobname, data_changes)
               VALUES ('EMPNO:' concat N.empno,
                      'U',
                      current user,
                      JOB_NAME,
                      'SALARY / BONUS CHANGE:'
                         CONCAT 'S:' 
                         CONCAT to_char (O.SALARY, '99, 999.99 ') - Italian Number Format (Decimal point)
                         CONCAT 'B:' 
                         CONCAT to_char (O.BONUS, '99, 999.99 ')
                         CONCAT '->'
                         CONCAT 'S:' 
                         CONCAT to_char (N.SALARY, '99, 999.99 ')
                         CONCAT 'B:' 
                         CONCAT to_char (N.BONUS, '99, 999.99 ')
                      );
         
      END IF;   
      

   END IF;
  END;

Step 3, let’s test the trigger

Let’s do a little test of the trigger, inserting an employee, making some changes and then deleting it from the employee table

---------------------------------------
- NOW WE CAN TRY OUT AUDIT LOG TRIGGER
----------------------------------------

- 1 Check EMPNO 999999 (Doesn't exists)
select * from sampledb.employee 
  where empno = '999999';
  
- 2 Insert EMPNO 999999
insert into sampledb.employee
 (empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, salary, bonus, comm)
values
 ('999999', 'Roberto', '', 'De Pedrini', 'B01', '0343', current date, 'CLEANER', '4', 'M', 1000, 1, 0);

- 3 WOW! I got promoted!
update sampledb.employee
 set JOB = 'VICE-P'
 where empno = '999999';   
 
- 4 OK now I want some more money!
update sampledb.employee
 set salary = salary + 5000
 where empno = '999999';   

- 5 Oh my God, the boss will fire me!
delete from sampledb.employee
where empno = '999999';

Step 4: Let’s read our AUDIT table … here, it was him!

- NOW LET'S CHECK OUR AUDIT LOG
select * from sampledb.employee_auditlog;

But if the table (File) is in use I cannot activate the trigger

The big advantage of SQL Trigger is that it can be “attached” and “detached” even with the table in use, as indicated in this GIST by Scott Forstie (Change trigger program in production) … forcing, just for a moment, the DB2 SQL engine rules

If I try to create a trigger, delete it or even just modify it with the table in use I get the SQL error “[SQL0913]Row or Object in use”, but with the Scotto Forstie trick we can insert in QAQQINI a momentary rule of ALLOW_DDL_CHANGES_WHILE_OPEN: it’s not fantastic ?

- HOW TO ACTIVATE / DISACTIVATE AND CHANGE TRIGGER WHEN TABLE IS "IN USE"
- Prepare to break the rules
call qsys2.override_qaqqini (1, '', '');
- Rules broken
call qsys2.override_qaqqini (2,  
                            'ALLOW_DDL_CHANGES_WHILE_OPEN', 
                            '* YES');
 
- Create / Delete or change your Trigger here
- ..
        
- Done breaking the rules
call qsys2.override_qaqqini (3, '', '');
    

Conclusion and references

The creation of a trigger like this is very simple, it takes 5 minutes but we have to decide which fields to check (in the case of updates). If we wanted a general trigger, which controls the modification of any field in the table, but always with a logic similar to this trigger, there is this excellent post by Michael Sansoterra on ITJungle, again from 2013: “Create a generic auditing trigger with SQL which with the SQL Descriptor technique passes all the fields of the table.

Simon Huthinson’s “Simple SQL Trigger” on RPGPGM.COM can also be a great starting point for SQL.

Did you like the article? Do you have any other suggestions?

But above all, you have chosen whether to be mice or gnomes (in reference to Spenser Johnson’s books): I put the link below, it’s in Affiliate Amazon, it’s as if you paid me a coffee!

#Everythingwillbefine

--- Roberto De Pedrini Faq400.com
About author

Founder of Faq400 Srl, IBM Champion, creator of Faq400.com and blog.faq400.com web sites. RPG developer since I was wearing shorts, strong IBM i supporter, I have always tried to share my knowledge with others through forums, events and courses. Now, with my company Faq400 Srl, I help companies to make the most of this great platform IBM i.

Leave a Reply

%d bloggers like this: