Oracle PL SQL real world example 1 - Triggers
In this series we will try to use different oracle SQL and PL SQL operations, commands etc, in context of real time or practical use in organizations.
After learning the syntax and practicing them let's try to apply with real world business problems.
In this blog we will try to cover to Log Audit activities which could be performed by different admin user via screen or back-end.
Audit is one of the important part in organization to keep track data changes, avoid wrong usage of data and action.
Let's take a look of business needs
Business Needs:
-------------------
Any activity performed in USER will be logged in separate log table which can be use by system admin or auditors to track the changes of user. It could be change of Branch , enable or disable user, new user creation , deleting user.
You may enhance this trigger as per business requirements.
About Triggers
--------------------
Triggers are PL/SQL codes execute automatically on certain events. which are insert/update/delete. These events could be before the action or after the action. We can create multiple triggers for each action or create a compound trigger and handle it based on certain events and actions.
The Actions and events are BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE, AFTER INSERT , AFTER UPDATE, AFTER DELETE
Triggers can be execute for each statement level or for each row. In below case we use FOR EACH ROW as we need to track every single change for every user.
create TM_USER table as below. It will be use for user information e.g. User ID , name etc. This table can be use by/from Oracle forms, oracle apex screen or any other application.
create table TM_USER
(
user_id VARCHAR2(30) not null,
user_name VARCHAR2(50) not null,
user_full_name VARCHAR2(250),
user_desg VARCHAR2(250),
user_level NUMBER,
user_branch VARCHAR2(30),
user_profit_centre VARCHAR2(30),
user_dept VARCHAR2(50),
manager_id VARCHAR2(10),
rm_id VARCHAR2(10),
rm_manager_id VARCHAR2(10),
user_phone VARCHAR2(50),
user_email VARCHAR2(50),
status VARCHAR2(1) default 'A',
add_by VARCHAR2(30),
add_date DATE,
modify_by VARCHAR2(30),
modify_date DATE
);
create audit log table as below. The audit log table will keep track of each changes performed by application for USER.
Remember, we are using a generic log table which keep track of activities from different table or screens. Hence we added log_object and log_action.
So in future if we need to track user_roles changes then we can use same audit log table with different object_name and log_action.
create table tm_audit_log (
log_object varchar2(100),
log_action varchar2(100),
user_id varchar2(100),
log_date date,
run_date date,
old_value varchar2(4000),
new_value varchar2(4000),
log_remarks varchar2(4000),
log_column VARCHAR2(50),
change_by varchar2(50)
);
create audit trigger as below. This is a compound trigger that will track insert/update/delete changes for every row. It also keep track of old and new values as well as some individual values like user enable/disable
CREATE OR REPLACE TRIGGER tm_user_audit_trg1
AFTER INSERT OR UPDATE OR DELETE ON tm_user
FOR EACH ROW
DECLARE
v_column_name VARCHAR2(50);
v_old_value VARCHAR2(4000);
v_new_value VARCHAR2(4000);
v_val varchar2(4000);
BEGIN
IF INSERTING THEN
INSERT INTO tm_audit_log (
log_object, log_action, user_id, log_date, run_date,
old_value, new_value, log_remarks, log_column, change_by
)
VALUES (
'TM_USER', 'CREATE', :new.user_id, SYSDATE, get_run_date,
NULL,
'user_name=' || :new.user_name || ', user_full_name=' || :new.user_full_name ||
', user_desg=' || :new.user_desg || ', user_level=' || :new.user_level ||
', user_branch=' || :new.user_branch || ', user_profit_centre=' || :new.user_profit_centre ||
', user_dept=' || :new.user_dept || ', manager_id=' || :new.manager_id ||
', rm_id=' || :new.rm_id || ', rm_manager_id=' || :new.rm_manager_id ||
', user_phone=' || :new.user_phone || ', user_email=' || :new.user_email ||
', status=' || :new.status || ', add_by=' || :new.add_by ||
', add_date=' || TO_CHAR(:new.add_date, 'YYYY-MM-DD HH24:MI:SS'),
'CREATE USER',
'ALL',
:new.add_by
);
ELSIF DELETING THEN
-- Log entire deleted row
INSERT INTO tm_audit_log (
log_object, log_action, user_id, log_date, run_date,
old_value, new_value, log_remarks, log_column, change_by
)
VALUES (
'TM_USER', 'DELETE', :old.user_id, SYSDATE, GET_RUN_DATE,
'user_name=' || :old.user_name || ', user_full_name=' || :old.user_full_name ||
', user_desg=' || :old.user_desg || ', user_level=' || :old.user_level ||
', user_branch=' || :old.user_branch || ', user_profit_centre=' || :old.user_profit_centre ||
', user_dept=' || :old.user_dept || ', manager_id=' || :old.manager_id ||
', rm_id=' || :old.rm_id || ', rm_manager_id=' || :old.rm_manager_id ||
', user_phone=' || :old.user_phone || ', user_email=' || :old.user_email ||
', status=' || :old.status || ', add_by=' || :old.add_by ||
', add_date=' || TO_CHAR(:old.add_date, 'YYYY-MM-DD HH24:MI:SS'),
NULL,
'DELETE USER',
'ALL',
TMUSER
);
ELSIF UPDATING THEN
IF NVL(:old.status, '-') != NVL(:new.status, '-') THEN
if NVL(:new.status, '-') = 'Y' then
v_val := 'ENABLE USER';
else
v_val := 'DISABLE USER';
end if;
INSERT INTO tm_audit_log (log_object, log_action, user_id, log_date, run_date,
old_value, new_value, log_remarks, log_column, change_by)
VALUES
(
'TM_USER', v_val, :new.user_id, SYSDATE, GET_RUN_DATE,
:old.status, :new.status, v_val, 'STATUS', :new.modify_by
);
END IF;
IF NVL(:old.user_branch, '-') != NVL(:new.user_branch, '-') THEN
v_val := 'BRANCH TRANSFER';
INSERT INTO tm_audit_log (log_object, log_action, user_id, log_date, run_date,
old_value, new_value, log_remarks, log_column, change_by)
VALUES
(
'TM_USER', 'UPDATE', :new.user_id, SYSDATE, GET_RUN_DATE,
:old.user_branch, :new.user_branch, v_val, 'USER_BRANCH', :new.modify_by
);
END IF;
IF NVL(:old.user_profit_centre, '-') != NVL(:new.user_profit_centre, '-') THEN
v_val := 'PROFIT CENTRE';
INSERT INTO tm_audit_log (log_object, log_action, user_id, log_date, run_date,
old_value, new_value, log_remarks, log_column, change_by)
VALUES
(
'TM_USER', 'UPDATE', :new.user_id, SYSDATE, GET_RUN_DATE,
:old.user_profit_centre, :new.user_profit_centre, v_val, 'USER_PROFIT_CENTRE', :new.modify_by
);
END IF;
IF NVL(:old.user_level, -1) != NVL(:new.user_level, -1) THEN
v_val := 'USER LEVEL';
INSERT INTO tm_audit_log (log_object, log_action, user_id, log_date, run_date,
old_value, new_value, log_remarks, log_column, change_by)
VALUES
(
'TM_USER', 'UPDATE', :new.user_id, SYSDATE, GET_RUN_DATE,
:old.user_level, :new.user_level, v_val, 'USER_LEVEL', :new.modify_by
);
END IF;
/*IF NVL(:old.user_full_name, '-') != NVL(:new.user_full_name, '-') THEN
INSERT INTO tm_audit_log VALUES (
'TM_USER', 'UPDATE', :new.user_id, SYSDATE, SYSDATE,
:old.user_full_name, :new.user_full_name, 'Updated user_full_name', 'user_full_name', :old.modify_by
);
END IF;
IF NVL(:old.user_desg, '-') != NVL(:new.user_desg, '-') THEN
INSERT INTO tm_audit_log VALUES (
'TM_USER', 'UPDATE', :new.user_id, SYSDATE, SYSDATE,
:old.user_desg, :new.user_desg, 'Updated user_desg', 'user_desg', :old.modify_by
);
END IF;
IF NVL(:old.user_level, -1) != NVL(:new.user_level, -1) THEN
INSERT INTO tm_audit_log VALUES (
'TM_USER', 'UPDATE', :new.user_id, SYSDATE, SYSDATE,
TO_CHAR(:old.user_level), TO_CHAR(:new.user_level), 'Updated user_level', 'user_level', :old.modify_by
);
END IF;*/
END IF;
END;
/
once you done above action try to perform different action on tm_user table like:
- -insert record in tm_user
- -update record in tm_user
- -update branch in tm_user
- -delete record in tm_user
- -disable and enable the user by changing the status flag to Y or N
check audit log table by
- select * from tm_audit_log
- sort the data by log_date
Hopefully it will clear the concepts of using oracle pl SQL triggers in real life.
These triggers can be use for various other actions as well some examples are as below
- Update inventory whenever any item purchase or sold
- Generate in flow or out flow when any debit or credit cash transactions performed
- Send mail when any order confirmed
- Alert if inventory stock is low
0 Comments