I was asked how to incorporate row level auditing detail into tables. While Temporal tables with Generated columns is a powerful combination, the following example demonstrates a different approach.

Row level auditing.sql

-- 
-- I was asked how to incorporate row level auditing detail into tables.
-- While Temporal tables with Generated columns is a powerful combination, 
-- the following example demonstrates a different approach.
--
-- One fun aspect about the solution is the use of INCLUDE SQL on the triggers... 
--
--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
-- Create or reset the SHOESTORE schema
--------------------------------------------------------------------------------------------------
-- DROP SCHEMA SHOESTORE CASCADE ;
CREATE SCHEMA SCOTTS_SUPER_SHOE_STORE FOR SCHEMA SHOESTORE ;
set schema SHOESTORE;
set path SHOESTORE, system path;

--------------------------------------------------------------------------------------------------
-- Customers table
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE TABLE SCOTTS_SUPER_SHOE_STORE.CUSTOMERS (
    CUSTOMER_ID FOR COLUMN CUSTID INTEGER GENERATED ALWAYS AS IDENTITY (
        START WITH 1 INCREMENT BY 1
        NO MINVALUE NO MAXVALUE
        NO CYCLE NO ORDER
        CACHE 20 
    ),
    CUSTOMER_NAME FOR COLUMN CUSTNAME VARCHAR(30) CCSID 37 NOT NULL ,
    CUSTOMER_ADDRESS FOR COLUMN CUSTADDR VARCHAR(300) CCSID 37 NOT NULL ,
    CUSTOMER_CITY FOR COLUMN CUSTCITY VARCHAR(30) CCSID 37 NOT NULL ,
    CUSTOMER_STATE FOR COLUMN CUSTSTATE CHAR(2) CCSID 37 NOT NULL ,
    CUSTOMER_PHONE FOR COLUMN CUSTPHONE CHAR(20) CCSID 37 NOT NULL ,
    CUSTOMER_EMAIL FOR COLUMN CUSTEMAIL VARCHAR(30) 
        CCSID 37 NOT NULL DEFAULT 'not set' ,
    CUSTOMER_TAX_ID FOR COLUMN CUSTTAXID CHAR(16) 
        CCSID 37 NOT NULL NOT NULL DEFAULT 'not set' ,
    CUSTOMER_DRIVERS_LICENSE_NUMBER FOR COLUMN CUSTLIC CHAR(16) 
        CCSID 37 NOT NULL DEFAULT 'not set' ,
    CUSTOMER_LOGIN_ID FOR COLUMN CUSTLOGIN VARCHAR(30) 
        CCSID 37 NOT NULL DEFAULT 'not set' ,
    CUSTOMER_SECURITY_QUESTION FOR COLUMN CUSTQUERY VARCHAR(100) 
        CCSID 37 NOT NULL DEFAULT 'not set' ,
    CUSTOMER_SECURITY_QUESTION_ANSWER FOR COLUMN CUSTANS VARCHAR(100) 
        CCSID 37 NOT NULL DEFAULT 'not set' ,
    -- Insert insight
    WHO_INSERTED FOR COLUMN WHOINS varchar(128) NOT NULL DEFAULT 
        user IMPLICITLY HIDDEN ,    
    JOB_INSERTED FOR COLUMN JOBINS varchar(28) NOT NULL IMPLICITLY HIDDEN ,    
    PGM_INSERTED FOR COLUMN PGMINS varchar(21) IMPLICITLY HIDDEN ,    
    INSERT_TIMESTAMP FOR COLUMN WHENINS TIMESTAMP NOT NULL DEFAULT 
        CURRENT_TIMESTAMP IMPLICITLY HIDDEN ,
    -- Update insight (most recent)
    WHO_UPDATED FOR COLUMN WHOUPD varchar(10) IMPLICITLY HIDDEN ,    
    JOB_UPDATED FOR COLUMN JOBUPD varchar(28) IMPLICITLY HIDDEN ,    
    PGM_UPDATED FOR COLUMN PGMUPD varchar(21) IMPLICITLY HIDDEN ,     
    UPDATE_TIMESTAMP FOR COLUMN WHENUPD TIMESTAMP 
        GENERATED ALWAYS FOR EACH ROW ON UPDATE 
        AS ROW CHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN,    CONSTRAINT SCOTTS_SUPER_SHOE_STORE.CUSTOMER_ID_PK PRIMARY KEY( CUSTOMER_ID ),
    CONSTRAINT SCOTTS_SUPER_SHOE_STORE.CUSTOMER_LOGIN_ID_UK UNIQUE( CUSTOMER_LOGIN_ID ) 
) ON REPLACE PRESERVE ROWS;

--------------------------------------------------------------------------------------------------
-- Accounts table
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE TABLE SCOTTS_SUPER_SHOE_STORE.ACCOUNTS (
    ACCOUNT_ID INTEGER GENERATED ALWAYS AS IDENTITY (
        START WITH 1 INCREMENT BY 1
        NO MINVALUE NO MAXVALUE
        NO CYCLE NO ORDER
        CACHE 20 
    ),
    CUSTOMER_ID FOR COLUMN CUSTID INTEGER NOT NULL ,
    ACCOUNT_NUMBER FOR COLUMN ACCOUNTNO VARCHAR(10) CCSID 37 NOT NULL DEFAULT '0000000000',
    ACCOUNT_NAME FOR COLUMN ACCOUNTNAM CHAR(50) CCSID 37 NOT NULL ,
    ACCOUNT_DATE_OPENED FOR COLUMN OPENDATE DATE DEFAULT CURRENT_DATE ,
    ACCOUNT_DATE_CLOSED FOR COLUMN CLOSEDATE DATE DEFAULT NULL ,
    ACCOUNT_CURRENT_BALANCE FOR COLUMN ACCTBAL DECIMAL(11, 2) NOT NULL DEFAULT 0,
    -- Insert insight
    WHO_INSERTED FOR COLUMN WHOINS varchar(128) NOT NULL DEFAULT 
        user IMPLICITLY HIDDEN ,    
    JOB_INSERTED FOR COLUMN JOBINS varchar(28) NOT NULL IMPLICITLY HIDDEN ,    
    PGM_INSERTED FOR COLUMN PGMINS varchar(21) IMPLICITLY HIDDEN ,    
    INSERT_TIMESTAMP FOR COLUMN WHENINS TIMESTAMP NOT NULL DEFAULT 
        CURRENT_TIMESTAMP IMPLICITLY HIDDEN ,
    -- Update insight (most recent)
    WHO_UPDATED FOR COLUMN WHOUPD varchar(10) IMPLICITLY HIDDEN ,    
    JOB_UPDATED FOR COLUMN JOBUPD varchar(28) IMPLICITLY HIDDEN ,    
    PGM_UPDATED FOR COLUMN PGMUPD varchar(21) IMPLICITLY HIDDEN ,        
    UPDATE_TIMESTAMP FOR COLUMN WHENUPD TIMESTAMP 
        GENERATED ALWAYS FOR EACH ROW ON UPDATE 
        AS ROW CHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN,    
    CONSTRAINT SCOTTS_SUPER_SHOE_STORE.ACCOUNT_ID_PK PRIMARY KEY( ACCOUNT_ID ),
    CONSTRAINT SCOTTS_SUPER_SHOE_STORE.ACCOUNT_CUSTOMER_ID_FK FOREIGN KEY( CUSTOMER_ID )
        REFERENCES SCOTTS_SUPER_SHOE_STORE.CUSTOMERS ( CUSTID )
        ON DELETE RESTRICT ON UPDATE RESTRICT 
) ON REPLACE PRESERVE ROWS; 

--------------------------------------------------------------------------------------------------
-- Transactions table
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE TABLE SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS FOR SYSTEM NAME TRANS (
    TRANSACTION_ID FOR COLUMN TRANSID INTEGER GENERATED ALWAYS AS IDENTITY (
        START WITH 1 INCREMENT BY 1
        NO MINVALUE NO MAXVALUE
        NO CYCLE NO ORDER
        CACHE 20 
    ), 
    ACCOUNT_ID INTEGER NOT NULL ,
    TRANSACTION_TYPE FOR COLUMN TRANTYPE CHAR( 1 ) CCSID 37 NOT NULL ,
    TRANSACTION_DATE FOR COLUMN TRANDATE DATE NOT NULL DEFAULT CURRENT_DATE ,
    TRANSACTION_TIME FOR COLUMN TRANTIME TIME NOT NULL DEFAULT CURRENT_TIME ,
    TRANSACTION_AMOUNT FOR COLUMN TRANAMT DECIMAL(11, 2) NOT NULL ,
    -- Insert insight
    WHO_INSERTED FOR COLUMN WHOINS varchar(128) NOT NULL DEFAULT 
        user IMPLICITLY HIDDEN ,    
    JOB_INSERTED FOR COLUMN JOBINS varchar(28) NOT NULL IMPLICITLY HIDDEN ,    
    PGM_INSERTED FOR COLUMN PGMINS varchar(21) IMPLICITLY HIDDEN ,    
    INSERT_TIMESTAMP FOR COLUMN WHENINS TIMESTAMP NOT NULL DEFAULT 
        CURRENT_TIMESTAMP IMPLICITLY HIDDEN ,
    -- Update insight (most recent)
    WHO_UPDATED FOR COLUMN WHOUPD varchar(10) IMPLICITLY HIDDEN ,    
    JOB_UPDATED FOR COLUMN JOBUPD varchar(28) IMPLICITLY HIDDEN ,    
    PGM_UPDATED FOR COLUMN PGMUPD varchar(21) IMPLICITLY HIDDEN ,     
    UPDATE_TIMESTAMP FOR COLUMN WHENUPD TIMESTAMP 
        GENERATED ALWAYS FOR EACH ROW ON UPDATE 
        AS ROW CHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN,
    CONSTRAINT SCOTTS_SUPER_SHOE_STORE.TRANSACTION_ID_PK PRIMARY KEY( TRANSACTION_ID ),
    CONSTRAINT SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS_ACCOUNT_ID_FK FOREIGN KEY( ACCOUNT_ID )
        REFERENCES SCOTTS_SUPER_SHOE_STORE.ACCOUNTS ( ACCOUNT_ID )
        ON DELETE RESTRICT
        ON UPDATE RESTRICT 
) ON REPLACE PRESERVE ROWS;

--------------------------------------------------------------------------------------------------
-- Public and private authorizations
--------------------------------------------------------------------------------------------------
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM" ;

CALL QSYS2.QCMDEXC('GRTOBJAUT OBJ(SHOESTORE) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*ALL)');
grant all on SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS to joeuser;

----------------------------------------------------------------------------------------------------
-- Triggers
----------------------------------------------------------------------------------------------------
call qsys2.ifs_write(PATH_NAME => '/home/SCOTTF/trigger_body_auditing', FILE_CCSID => 37, OVERWRITE => 'REPLACE', 
LINE => '    declare @pgm varchar(21) for sbcs data;
    -- Find most recent user/user program
    select x.PROGRAM_LIBRARY_NAME concat ''/'' concat x.PROGRAM_NAME
      into @pgm
      from table (
             QSYS2.STACK_INFO(''*'')
           ) as x
           inner join qsys2.program_info p
             on x.PROGRAM_LIBRARY_NAME = p.PROGRAM_LIBRARY and
               x.PROGRAM_NAME = p.PROGRAM_NAME
      where LIC_PROCEDURE_NAME is NULL and
            program_state = ''*USER'' and
            program_domain = ''*USER''
      order by ORDINAL_POSITION desc
      limit 1;
    -- Else... find most recent non-QSYS program
    if (@pgm is null) then
      select x.PROGRAM_LIBRARY_NAME concat ''/'' concat x.PROGRAM_NAME
        into @pgm
        from table (
            QSYS2.STACK_INFO(''*'')
          ) as x
        where x.PROGRAM_LIBRARY_NAME <> ''QSYS''
        order by ORDINAL_POSITION desc
        limit 1;
    end if;
    -- Else... find top most QSYS program
    if (@pgm is null) then
      select x.PROGRAM_LIBRARY_NAME concat ''/'' concat x.PROGRAM_NAME
        into @pgm
        from table (
            QSYS2.STACK_INFO(''*'')
          ) as x
        order by ORDINAL_POSITION asc
        limit 1;
    end if;

    if (inserting) then
      set n.JOB_INSERTED = qsys2.job_name;
      set n.PGM_INSERTED = @pgm;
    else -- Must be Updating
      set n.WHO_UPDATED = user;
      set n.PGM_UPDATED = @pgm;
      set n.JOB_UPDATED = qsys2.job_name;
    end if;
    ');


create or replace trigger SCOTTS_SUPER_SHOE_STORE.CUSTOMERS_AUDIT_TRIGGER
    BEFORE update or insert on SCOTTS_SUPER_SHOE_STORE.CUSTOMERS
    referencing new as n old as o for each row
  when (inserting or updating)
begin atomic
  INCLUDE SQL '/home/SCOTTF/trigger_body_auditing';
end;

create or replace trigger SCOTTS_SUPER_SHOE_STORE.ACCOUNTS_AUDIT_TRIGGER
    BEFORE update or insert on SCOTTS_SUPER_SHOE_STORE.ACCOUNTS
    referencing new as n old as o for each row  
set option output=*print
  when (inserting or updating)
begin atomic
  INCLUDE SQL '/home/SCOTTF/trigger_body_auditing';
end;

create or replace trigger SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS_AUDIT_TRIGGER
    BEFORE update or insert on SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS
    referencing new as n old as o for each row
  when (inserting or updating)
begin atomic
  INCLUDE SQL '/home/SCOTTF/trigger_body_auditing';
end;
stop;

----------------------------------------------------------------------------------------------------
-- Load some sample data into CUSTOMERS
----------------------------------------------------------------------------------------------------
INSERT INTO SCOTTS_SUPER_SHOE_STORE.CUSTOMERS
(CUSTOMER_ID,	
CUSTOMER_NAME,	
CUSTOMER_ADDRESS,	
CUSTOMER_CITY,	
CUSTOMER_STATE,	
CUSTOMER_PHONE,	
CUSTOMER_EMAIL,	
CUSTOMER_TAX_ID,	
CUSTOMER_DRIVERS_LICENSE_NUMBER,	
CUSTOMER_LOGIN_ID,	
CUSTOMER_SECURITY_QUESTION,	
CUSTOMER_SECURITY_QUESTION_ANSWER)
 VALUES (DEFAULT, 'Becky Silver', 'King''s Cross Station Platform 9-3/4',        
        'London', 'UK', '+44-1475-898-073', '[email protected]',
        'GB999 9999 73', 'ABCDE123456AB1AB', 'Bs1lver', 
        'Who has the best football team?', 'Manchester United'),
        (DEFAULT, 'Sammie Gold', '20 Deans Yd',        
        'London', 'UK', '+44-20-7222-5152', '[email protected]',
        'GB888 8888 11', 'GEEE0101011CDDFE', 'sg0lden', 
        'Who has the best football team?', 'Manchester City'),
        (DEFAULT, 'Roger Moore', '85 Albert EmSHOESTOREment',        
        'London', 'UK', '+44-99-0077-0077', '[email protected]',
        'GB888 8888 11', 'GFFF0070077TTRED', 'james', 
        'Who has the best football team?', 'Arsenal'),
        (DEFAULT, 'John Cleese', '162-168 Regent St.',        
        'London', 'UK', '+44-99-0077-0077', '[email protected]',
        'GB4444 4444 22', 'GEEE911911911BLU', 'SirJohn', 
        'Who has the best football team?', 'Shrubbery');

UPDATE SCOTTS_SUPER_SHOE_STORE.CUSTOMERS SET CUSTOMER_CITY = UPPER(CUSTOMER_CITY);

--------------------------------------------------------------------------------------------------
-- End - Load some sample data into CUSTOMERS
--------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
-- Load some sample data into ACCOUNTS
----------------------------------------------------------------------------------------------------
INSERT INTO SCOTTS_SUPER_SHOE_STORE.ACCOUNTS
(ACCOUNT_ID,	
CUSTOMER_ID,	
ACCOUNT_NUMBER,	
ACCOUNT_NAME,	
ACCOUNT_DATE_OPENED,	
ACCOUNT_DATE_CLOSED,	
ACCOUNT_CURRENT_BALANCE,	
INSERT_TIMESTAMP,	
UPDATE_TIMESTAMP)
 VALUES (DEFAULT, 1, 'CHK100112P', 'Becky''s checking account',        
        CURRENT_DATE - 7 days, NULL, 1034.44, DEFAULT, DEFAULT),
        (DEFAULT, 2, 'CHK100332P', 'Sammie''s checking account',        
        CURRENT_DATE - 7 days, NULL, 5055.55, DEFAULT, DEFAULT),
        (DEFAULT, 3, 'CHK100443O', 'Roger''s checking account',        
        CURRENT_DATE - 7 days, NULL, 43.01, DEFAULT, DEFAULT),
        (DEFAULT, 4, 'CHK100554G', 'John''s gold checking account',        
        CURRENT_DATE - 7 days, NULL, 69054.13, DEFAULT, DEFAULT);
 
----------------------------------------------------------------------------------------------------
-- End --- Load some sample data into ACCOUNTS
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
-- Load some sample data into TRANSACTIONS
----------------------------------------------------------------------------------------------------
INSERT INTO SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS
(TRANSACTION_ID,	
ACCOUNT_ID,	
TRANSACTION_TYPE,	
TRANSACTION_DATE,	
TRANSACTION_TIME,	
TRANSACTION_AMOUNT,	
INSERT_TIMESTAMP,	
UPDATE_TIMESTAMP)
 VALUES (DEFAULT, 1, 'D', CURRENT_DATE, CURRENT_TIME, 10.00, DEFAULT, DEFAULT),
        (DEFAULT, 2, 'W', CURRENT_DATE, CURRENT_TIME, 100.00, DEFAULT, DEFAULT),
        (DEFAULT, 3, 'W', CURRENT_DATE, CURRENT_TIME, 5.00, DEFAULT, DEFAULT),
        (DEFAULT, 4, 'W', CURRENT_DATE, CURRENT_TIME, 1000.00, DEFAULT, DEFAULT),
        (DEFAULT, 4, 'D', CURRENT_DATE, CURRENT_TIME, 5055.25, DEFAULT, DEFAULT);

----------------------------------------------------------------------------------------------------
-- End --- Load some sample data into TRANSACTIONS
----------------------------------------------------------------------------------------------------
stop;
-- Update some things
cl: crtusrprf joeuser password();
set session authorization joeuser ;
update SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS set TRANSACTION_AMOUNT = TRANSACTION_AMOUNT + 1;

select TRANSACTION_ID, ACCOUNT_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TRANSACTION_TIME,
       TRANSACTION_AMOUNT, WHO_INSERTED, JOB_INSERTED, PGM_INSERTED, INSERT_TIMESTAMP,
       WHO_UPDATED, JOB_UPDATED, PGM_UPDATED, UPDATE_TIMESTAMP
  from SCOTTS_SUPER_SHOE_STORE.TRANSACTIONS;