SQL – Audit trigger … chi ha spostato il mio formaggio!

Last Updated on 13 Marzo 2020 by Roberto De Pedrini

I sorgenti del trigger e del test di questo Post sono disponibile sul nostro GIST di Github:

Avete letto il libro di Spencer Johnson “Chi ha spostato il mio formaggio?” … se non lo avete fatto vi consiglio di farlo … è una semplice parabola sul cambiamento, nella vita, nel lavoro, nella famiglia. Nel libro, il formaggio rappresenta i nostri desideri, un buon lavoro, una bella famiglia, un po’ di soldi sul conto corrente … ma le cose cambiano e il problema è che le scorte di formaggio prima o poi finiscono.

Ma cosa c’entra il formaggio con un trigger SQL per l’Audit dei cambiamenti in una tabella? Niente, non c’entra niente, ma il titolo potrebbe fare molto “click-baiting” stimolando proprio la curiosità!

E’ solo che questo post nasce in realtà da un piccola discussione con un cliente (C):

• (C) “Voglio sapere chi ha cambiato il codice pagamento su quell’ordine!”
• (io) “OK, io, invece, vorrei sapere chi ha spostato il mio formaggio”
• (C) “Ma cosa c’entra il formaggio … voglio sapere chi ha cambiato quel pagamento!”

Sulla tabella degli ordini non c’era nessun log, solo data, ora e utente che ha fatto l’ultima modifica … ma non c’era nessuna certezza che fosse colui che ha cambiato il codice di pagamento.

OK, la modifica può essere fatta da diversi programmi, non voglio mettere le mani a tutti, potrei attivare un trigger con la procedura normale dei trigger ADDPFTRG … ma la tabella degli ordini è sempre in uso, o dagli utenti o dai web service del B2B e della Intranet …

E’ una ottima occasione per pensare ad un trigger SQL che, con un trucchetto suggerito da Scott Forstie, è possibile attivare anche con la tabella in uso.

Non vi riporterò l’esempio reale sulla mia tabella degli ordini, cercherò invece di creare un trigger di Audit sulla solita tabella EMPLOYEE della libreria SAMPLEDB che utilizzo spesso in questo Blog.

Il sorgente SQL di questo post, se volete fare del copia-incolla, lo trovate anche in questo Github Gist, copiate da lì che non viene “rovinato” da WordPress. essenziali:

Prerequisiti per l’esempio: SAMPLEDB

L’esempio qui riportato si basa sulla tipica tabella EMPLOYEE di una SAMPLEDB, una libreria (SQL SCHEMA) con semplici esempi di tabelle relazionate tra loro

CALL QSYS.CREATE_SQL_SAMPLE(‘SAMPLEDB’)

Passo 1, la tabella di LOG

Iniziamo a creare la nostra tabella di Log … dove andremo a salvare gli eventi e le modifiche della effettuate sulla nostra tabella principale

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

Passo 2, creiamo il nostro Trigger

Creiamo il trigger sulla tabella EMPLOYEE che monitorizza gli eventi di INSERT/DELETE/UPDATE: Per l’INSERT e il DELETE andremo semplicemente a loggare utente e job che ha fatto l’operazione e il riferimento al nostro EMPNO, codice dipendete. Per l’UPDATE andiamo invece in dettaglio a controllare alcuni campi specifici della nostra tabella, rendendo il file di AUDIT leggibile dall’utente:

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

Passo 3, testiamo il trigger

Facciamo un piccolo test del trigger, inserendo un dipendente, facendone alcune modifiche e quindi cancellandolo dalla tabella dei dipendenti

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

Passo 4: Leggiamo la nostra tabella di AUDIT … ecco, è stato lui!

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

Ma se la tabella (File) è in uso non posso attivare il trigger

Il grosso vantaggio del Trigger SQL è che può essere “attaccato” e “staccato” anche con la tabella in uso, come indicato in questo GIST di Scott Forstie (Change trigger program in production)… forzando, solo per un attimo, le regole del motore DB2 SQL

Se cerco di creare un trigger, cancellarlo o anche solo modificarlo con la tabella in uso ottengo l’errore SQL “[SQL0913] Row or Object in use”, ma con il trick di Scotto Forstie possiamo inserire in QAQQINI una regola momentanea di ALLOW_DDL_CHANGES_WHILE_OPEN: non è fantastico?

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

Conclusione e riferimenti

La creazione di un trigger come questo è molto semplice, bastano 5 minuti ma dobbiamo decidere quali campi controllare (nel caso di update). Se volessimo un trigger generale, che controlla la modifica di qualsiasi campo della tabella, ma sempre con una logica simile a questo trigger c’è questo ottimo post di Michael Sansoterra su ITJungle, ancora del 2013: “Create a generic auditing trigger with SQL“ che con la tecnica degli SQL Descriptor passa tutti i campi della tabella.

Anche “Simple SQL Trigger” di Simon Huthinson su RPGPGM.COM può essere un ottimo spunto per gli SQL.

L’articolo vi è piaciuto? Avete qualche altro suggerimento?

Oltre a leggervi i post indicati sopra vi invito anche a leggervi i libri di Spencer Johnson, “Chi ha spostato il mio formaggio” e la sua continuazione … vi metto qui sotto i link Amazon se volete acquistarli o scaricarli sul vostro Kindle.

#AndraTuttoBene

--- Roberto De Pedrini Faq400.com