Posts Tagged ‘know dml

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



Blog Stats

  • 36,380 hits

RSS World News

  • After ISIS, 'Mosul can never be the same' October 17, 2017
    A few days ago, Mahmoud Saeed boarded a plane for a trip that is sure to be filled with trepidation.
  • What's next for ISIS after the fall of Raqqa? October 18, 2017
    US-backed forces in Raqqa said Tuesday that major military operations against ISIS had ended as they worked to clear out the remnants of sleeper cells and mines in the ruins of the Syrian city.
  • Video shows Raqqa execution spot October 18, 2017
    This exclusive drone footage shows empty streets and destroyed buildings in Raqqa, a city on the brink of being liberated from ISIS.
  • ISIS arrests: How many are from travel ban countries? October 17, 2017
    They called it the "US Project." Their goal, allegedly, was to carry out an ISIS terror attack on New York during Ramadan that would "show them what they hadn't seen in 2001."
  • US appeals for calm as allies clash in Iraq October 17, 2017
    The United States has made an urgent call for calm in northern Iraq as Iraqi forces and Kurdish fighters -- two of Washington's key allies in the region -- clash over disputed territory.