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;