Skip to content

Instantly share code, notes, and snippets.

@Faq400Git
Created March 13, 2020 10:59
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save Faq400Git/b8b5fe283ffa993940115137da33e745 to your computer and use it in GitHub Desktop.
Simple AUDIT trigger SQL DB2 FOR i
-------------------------------------------------------------
-- THIS MATERIAL IS PART OF MY POST BLOG
-------------------------------------------------------------
--------------------------------
-- FIRST CREATE AN AUDIT 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);
---------------------------------------------
-- 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 ;
---------------------------------------
-- 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';
-- NOW LET'S CHECK OUR AUDIT LOG
select * from sampledb.employee_auditlog;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment