11
Oct
10

How to Recognize DML Origins within Table

Pernahkah anda mengalami kondisi dimana nilai pada suatu kolom di Table X berubah tanpa sepengetahuan kita, atau, dengan kata lain, kita tidak tahu SQL Object (Packages, Function, etc) yang melakukan DML terhadap Table X?

Berikut ini contoh untuk memonitornya menggunakan bantuan trigger.. (kasus DML Update)

CREATE OR REPLACE TRIGGER <schema>.<trigger_name>
 BEFORE UPDATE OF <nama_kolom_yg_mau_dimonitor>
 ON <schema>.<nama_table>
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
 vchstr      VARCHAR2 (32767);
BEGIN
 FOR arec IN (SELECT USER, program, machine, obj.object_name,sq.sql_text
 FROM v$session s,
 v$sql sq
 ,dba_objects obj
 WHERE s.audsid = USERENV ('sessionid')
 AND sq.sql_id = s.sql_id
 and obj.object_id = sq.program_id)
 LOOP
 vchstr :=
 'USER:'
 || TRIM (arec.USER)
 || '_PROG:'
 || TRIM (arec.program)
 || '_@:'
 || TRIM (arec.object_name)
 || '@SQL:'
 || TRIM (arec.sql_text);
 EXIT;
 END LOOP;

 <panggil program untuk menulis VCHSTR ke dalam log table>

EXCEPTION
 WHEN OTHERS
 THEN
 -- Consider logging the error and then re-raise
 RAISE;
END;

That’s it. Hope it helps..

Advertisements

0 Responses to “How to Recognize DML Origins within Table”



  1. Leave a Comment

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


Blog Stats

  • 36,321 hits

RSS World News


%d bloggers like this: