Table DML Detection

Overview

Auditing is the most robust way to track changes in a database. However, another way to detect DML on a table is via a trigger. In this way DML events can be detected and logged given your unique specifications. This is not meant to be used on a table with extremely high activity of course. Change this to meet your specs.

To achieve this:

1. Create a centralized table for storing these type events. In this example: appdba.tracking
2. Grant required privileges to the above table to the schema with table to track.
3. For each table to track, create a trigger in the corresponding schema.

Architecture Creation Scenario

Create Table to Store Tracking Events

DROP TABLE appdba.tracking;

CREATE TABLE appdba.tracking (
  tracking_id   number,
  event         varchar2(50) CONSTRAINT tracking_event_nn  NOT NULL, 
  object        varchar2(50) CONSTRAINT tracking_object_nn NOT NULL,
  dml_date      date,
  CONSTRAINT tracking_pk PRIMARY KEY(tracking_id) USING INDEX TABLESPACE users
) TABLESPACE users;

INSERT INTO appdba.tracking(tracking_id,event,object,dml_date ) 
VALUES (1001,'Tracking table initialized.','appdba.tracking',SYSDATE);

SELECT * FROM appdba.tracking;

Grant Privileges

DEFINE Schema2Track = "SCOTT"
GRANT SELECT,INSERT ON appdba.tracking TO &Schema2Track;

Get Tablename to Track

SELECT table_name FROM dba_tables WHERE owner='&Schema2Track';

Create Trigger (for each table to track)

This example tracks updates via AFTER UPDATE trigger option. Change as required. Change for each table to run below script on.

DEFINE Table2Track = "FININSTITUTIONS"

CREATE OR REPLACE TRIGGER &Schema2Track..trk_&Table2Track 
AFTER UPDATE ON &Schema2Track..&Table2Track
FOR EACH ROW 
DECLARE
   vNewID  NUMBER;
BEGIN
   -- Get tracking_id
   SELECT max(tracking_id)+1 INTO vNewID FROM appdba.tracking;

   -- INSERT
   INSERT INTO appdba.tracking 
   VALUES (vNewID,'Table DML','&Schema2Track..&Table2Track',SYSDATE);
EXCEPTION
   WHEN OTHERS THEN
   if sqlcode = -4091 then 
     null; --ignore mutation errors 
   else 
     raise; 
   end if;
END;
/

-- QC

SELECT trigger_name, table_name, status 
FROM dba_triggers WHERE owner = '&Schema2Track';

Display DML Events

COL event  FORMAT a30
COL object FORMAT a30
SELECT * FROM appdba.tracking ORDER BY tracking_id DESC;

<- Security