Audit Master Table by Trigger

Misal Nama Table yang kita mau audit adalah table M_PRICE dan kita mau mengaudit setiap ada activity Add, Update and Delete terhadadap table ini.
Kita perlu 2 table untuk mengaudit, satu untuk mencatat data2 yang diubah (tracking), satu table lagi digunakan untuk mencatat summary sudah berapa kali berubah master table tersebut.

M_PRICE ==> master table

M_PRICE_A ==> Audit table

M_PRICE_C ==> tracking berapa kali berubah

Dan berikut Triger yang bisa kita buat dengan nama M_PRICE_T

 

create or replace
TRIGGER M_PRICE_T BEFORE INSERT OR DELETE OR UPDATE ON M_PRICE FOR EACH ROW
DECLARE
l_change_num NUMBER;
BEGIN
— Increment the change_num for this table and keep the new value
UPDATE M_PRICE_C set change_num = change_num+1 returning change_num into l_change_num;

— Insert appropriate audit row(s)
IF INSERTING THEN
INSERT INTO M_PRICE_A(PRICE_ID, PRICE_NAME, change_num, aud_action, aud_timestamp) VALUES(:new.PRICE_ID, :new.PRICE_NAME, l_change_num, ‘I’, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO M_PRICE_A(PRICE_ID, PRICE_NAME, change_num, aud_action, aud_timestamp) VALUES(:new.PRICE_ID, :new.PRICE_NAME, l_change_num, ‘AU’, SYSDATE);
INSERT INTO M_PRICE_A(PRICE_ID, PRICE_NAME, change_num, aud_action, aud_timestamp) VALUES(:old.PRICE_ID, : old.PRICE_NAME, l_change_num, ‘BU’, SYSDATE);
ELSE
INSERT INTO M_PRICE_A(PRICE_ID, PRICE_NAME, change_num, aud_action, aud_timestamp) VALUES(:old.PRICE_ID, : old.PRICE_NAME, l_change_num, ‘D’, SYSDATE);
END IF;
END M_PRICE_T;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: