Created
March 13, 2020 10:59
Star
You must be signed in to star a gist
Simple AUDIT trigger SQL DB2 FOR i
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
------------------------------------------------------------- | |
-- 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