Auditing Table Using DML Trigger
Process:
1. First you need to grant 3 privileges in your system.
grant dba,select any dictionary,select_catalog_role to hr;
2. Then connect your hr user.
3. After access your user you need to create 2 tables name LOGGING_DATA_HDR and
LOGGING_DATA_DTL and also a SEQUENCE name SEQUENCE LOG_ID_SEQ
CREATE TABLE LOGGING_DATA_HDR
(
LOG_ID NUMBER,
TABLE_NAME VARCHAR2 (30 CHAR) NOT NULL,
PK_DATA VARCHAR2 (500 BYTE),
ROW_ID ROWID NOT NULL,
LOG_DATE DATE NOT NULL,
OPERATION_TYPE VARCHAR2 (1 BYTE) NOT NULL,
DB_USER VARCHAR2 (100 BYTE),
CLIENT_IP VARCHAR2 (40 BYTE),
CLIENT_HOST VARCHAR2 (100 BYTE),
CLIENT_OS_USER VARCHAR2 (100 BYTE),
APP_USER VARCHAR2 (50 BYTE)
);
ALTER TABLE LOGGING_DATA_HDR ADD (
CONSTRAINT LOGGING_DATA_HDR_PK
PRIMARY KEY
(LOG_ID)
);
CREATE TABLE LOGGING_DATA_DTL
(
LOG_ID NUMBER,
COLUMN_NAME VARCHAR2 (30 CHAR),
OLD_VALUE VARCHAR2 (4000 BYTE),
NEW_VALUE VARCHAR2 (4000 BYTE)
);
ALTER TABLE LOGGING_DATA_DTL ADD (
CONSTRAINT LOGGING_DATA_DTL_PK
PRIMARY KEY
(LOG_ID, COLUMN_NAME));
CREATE SEQUENCE LOG_ID_SEQ
START WITH 1
MAXVALUE 99999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
4. Then you need to create this package and package body:
CREATE OR REPLACE PACKAGE DML_LOG
AS
TYPE GT$LOGGING_DATA_DTL IS TABLE OF LOGGING_DATA_DTL%ROWTYPE;
GC$APP_USER LOGGING_DATA_HDR.APP_USER%TYPE;
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2);
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE);
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE;
END DML_LOG;
/
CREATE OR REPLACE PACKAGE BODY DML_LOG
AS
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2)
IS
LN$LOG_ID LOGGING_DATA_HDR.LOG_ID%TYPE;
BEGIN
SELECT LOG_ID_SEQ.NEXTVAL INTO LN$LOG_ID FROM DUAL;
INSERT INTO LOGGING_DATA_HDR (LOG_ID,
TABLE_NAME,
PK_DATA,
ROW_ID,
LOG_DATE,
OPERATION_TYPE,
DB_USER,
CLIENT_IP,
CLIENT_HOST,
CLIENT_OS_USER,
APP_USER)
VALUES (LN$LOG_ID,
IN_TABLE_NAME,
GET_COMPOSITE_KEY (IN_TABLE_NAME, IN_ROWID),
IN_ROWID,
SYSDATE,
IN_OPERATION_TYPE,
SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
SYS_CONTEXT ('USERENV', 'ip_address'),
SYS_CONTEXT ('USERENV', 'host'),
SYS_CONTEXT ('USERENV', 'os_user'),
GET_APP_USER);
IF IN_ARRAY IS NOT NULL AND IN_ARRAY.COUNT > 0
THEN
FOR INDX IN IN_ARRAY.FIRST .. IN_ARRAY.LAST
LOOP
IF IN_ARRAY (INDX).COLUMN_NAME IS NOT NULL
AND (IN_ARRAY (INDX).OLD_VALUE IS NOT NULL
OR IN_ARRAY (INDX).NEW_VALUE IS NOT NULL)
THEN
INSERT INTO LOGGING_DATA_DTL (LOG_ID,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE)
VALUES (LN$LOG_ID,
IN_ARRAY (INDX).COLUMN_NAME,
IN_ARRAY (INDX).OLD_VALUE,
IN_ARRAY (INDX).NEW_VALUE);
END IF;
END LOOP;
END IF;
END ADD_LOG;
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2
IS
LC$TRIGGER_STMT VARCHAR2 (4000);
CURSOR LCUR$COLUMNS
IS
SELECT COLUMN_NAME
FROM USER_TAB_COLS
WHERE TABLE_NAME = IN_TABLE_NAME
ORDER BY COLUMN_ID;
BEGIN
LC$TRIGGER_STMT :=
'CREATE OR REPLACE TRIGGER '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG '
|| CHR (10)
|| 'AFTER INSERT OR UPDATE OR DELETE'
|| CHR (10)
|| 'ON '
|| IN_TABLE_NAME
|| ' FOR EACH ROW '
|| CHR (10)
|| 'DECLARE '
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL DML_LOG.GT$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'LC$OPERATION VARCHAR2 (1);'
|| CHR (10)
|| 'PROCEDURE ADD_ELEMENT ('
|| CHR (10)
|| 'IN_OPERATION VARCHAR2,'
|| CHR (10)
|| 'IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,'
|| CHR (10)
|| 'IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,'
|| CHR (10)
|| 'IN_NEW_VALUE LOGGING_DATA_DTL.NEW_VALUE%TYPE)'
|| CHR (10)
|| 'IS'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| ' IF NOT (IN_OPERATION = ''U'' AND IN_NEW_VALUE = IN_OLD_VALUE)'
|| CHR (10)
|| 'THEN'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL.EXTEND;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'END IF;'
|| CHR (10)
|| 'END ADD_ELEMENT;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL := DML_LOG.GT$LOGGING_DATA_DTL ();'
|| CHR (10)
|| 'LC$OPERATION :='
|| CHR (10)
|| 'CASE WHEN INSERTING THEN ''I'' WHEN UPDATING THEN ''U'' ELSE ''D'' END;'
|| CHR (10);
FOR LREC$COLUMNS IN LCUR$COLUMNS
LOOP
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' ADD_ELEMENT (LC$OPERATION,'''
|| LREC$COLUMNS.COLUMN_NAME
|| ''',:OLD.'
|| LREC$COLUMNS.COLUMN_NAME
|| ',:NEW.'
|| LREC$COLUMNS.COLUMN_NAME
|| ');'
|| CHR (10);
END LOOP;
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' DML_LOG.ADD_LOG (LT$LOGGING_DATA_DTL,'''
|| IN_TABLE_NAME
|| ''',:NEW.ROWID,LC$OPERATION);'
|| CHR (10)
|| 'END '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG ;';
RETURN LC$TRIGGER_STMT;
END GENERATE_TRIGGER;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
LC$COLUMNS VARCHAR2 (512) := '';
LC$KEY VARCHAR2 (512);
CURSOR LCUR$COLUMNS (
IN_TABLE_NAME VARCHAR2)
IS
SELECT CON_C.COLUMN_NAME
FROM USER_CONS_COLUMNS CON_C, USER_CONSTRAINTS CON
WHERE CON.CONSTRAINT_NAME = CON_C.CONSTRAINT_NAME
AND CON.CONSTRAINT_TYPE = 'P'
AND CON.TABLE_NAME = IN_TABLE_NAME
ORDER BY POSITION;
BEGIN
FOR LREC$COLUMNS IN LCUR$COLUMNS (IN_TABLE)
LOOP
LC$COLUMNS :=
LC$COLUMNS
|| LREC$COLUMNS.COLUMN_NAME
|| '||'''
|| IN_DELIMETER
|| '''||';
END LOOP;
LC$COLUMNS := RTRIM (LC$COLUMNS, '||''' || IN_DELIMETER || '''||');
EXECUTE IMMEDIATE
'SELECT '
|| LC$COLUMNS
|| ' FROM '
|| IN_TABLE
|| ' WHERE ROWID='''
|| IN_ROWID
|| ''''
INTO LC$KEY;
RETURN LC$KEY;
END GET_COMPOSITE_KEY;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE)
IS
BEGIN
GC$APP_USER := IN_APP_USER;
END SET_APP_USER;
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE
IS
BEGIN
RETURN GC$APP_USER;
END GET_APP_USER;
END DML_LOG;
/
5. Then create this trigger to generate automatically log register trigger.
set serveroutput on
BEGIN
DBMS_OUTPUT.PUT_LINE (DML_LOG.GENERATE_TRIGGER ('DEPARTMENTS'));
END;
/
6. Copy the trigger and paste it in SQL command line for createing this trigger.
7. After that genarate a DML statement.
8. commit the DML.
1. First you need to grant 3 privileges in your system.
grant dba,select any dictionary,select_catalog_role to hr;
2. Then connect your hr user.
3. After access your user you need to create 2 tables name LOGGING_DATA_HDR and
LOGGING_DATA_DTL and also a SEQUENCE name SEQUENCE LOG_ID_SEQ
CREATE TABLE LOGGING_DATA_HDR
(
LOG_ID NUMBER,
TABLE_NAME VARCHAR2 (30 CHAR) NOT NULL,
PK_DATA VARCHAR2 (500 BYTE),
ROW_ID ROWID NOT NULL,
LOG_DATE DATE NOT NULL,
OPERATION_TYPE VARCHAR2 (1 BYTE) NOT NULL,
DB_USER VARCHAR2 (100 BYTE),
CLIENT_IP VARCHAR2 (40 BYTE),
CLIENT_HOST VARCHAR2 (100 BYTE),
CLIENT_OS_USER VARCHAR2 (100 BYTE),
APP_USER VARCHAR2 (50 BYTE)
);
ALTER TABLE LOGGING_DATA_HDR ADD (
CONSTRAINT LOGGING_DATA_HDR_PK
PRIMARY KEY
(LOG_ID)
);
CREATE TABLE LOGGING_DATA_DTL
(
LOG_ID NUMBER,
COLUMN_NAME VARCHAR2 (30 CHAR),
OLD_VALUE VARCHAR2 (4000 BYTE),
NEW_VALUE VARCHAR2 (4000 BYTE)
);
ALTER TABLE LOGGING_DATA_DTL ADD (
CONSTRAINT LOGGING_DATA_DTL_PK
PRIMARY KEY
(LOG_ID, COLUMN_NAME));
CREATE SEQUENCE LOG_ID_SEQ
START WITH 1
MAXVALUE 99999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
4. Then you need to create this package and package body:
CREATE OR REPLACE PACKAGE DML_LOG
AS
TYPE GT$LOGGING_DATA_DTL IS TABLE OF LOGGING_DATA_DTL%ROWTYPE;
GC$APP_USER LOGGING_DATA_HDR.APP_USER%TYPE;
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2);
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE);
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE;
END DML_LOG;
/
CREATE OR REPLACE PACKAGE BODY DML_LOG
AS
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2)
IS
LN$LOG_ID LOGGING_DATA_HDR.LOG_ID%TYPE;
BEGIN
SELECT LOG_ID_SEQ.NEXTVAL INTO LN$LOG_ID FROM DUAL;
INSERT INTO LOGGING_DATA_HDR (LOG_ID,
TABLE_NAME,
PK_DATA,
ROW_ID,
LOG_DATE,
OPERATION_TYPE,
DB_USER,
CLIENT_IP,
CLIENT_HOST,
CLIENT_OS_USER,
APP_USER)
VALUES (LN$LOG_ID,
IN_TABLE_NAME,
GET_COMPOSITE_KEY (IN_TABLE_NAME, IN_ROWID),
IN_ROWID,
SYSDATE,
IN_OPERATION_TYPE,
SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
SYS_CONTEXT ('USERENV', 'ip_address'),
SYS_CONTEXT ('USERENV', 'host'),
SYS_CONTEXT ('USERENV', 'os_user'),
GET_APP_USER);
IF IN_ARRAY IS NOT NULL AND IN_ARRAY.COUNT > 0
THEN
FOR INDX IN IN_ARRAY.FIRST .. IN_ARRAY.LAST
LOOP
IF IN_ARRAY (INDX).COLUMN_NAME IS NOT NULL
AND (IN_ARRAY (INDX).OLD_VALUE IS NOT NULL
OR IN_ARRAY (INDX).NEW_VALUE IS NOT NULL)
THEN
INSERT INTO LOGGING_DATA_DTL (LOG_ID,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE)
VALUES (LN$LOG_ID,
IN_ARRAY (INDX).COLUMN_NAME,
IN_ARRAY (INDX).OLD_VALUE,
IN_ARRAY (INDX).NEW_VALUE);
END IF;
END LOOP;
END IF;
END ADD_LOG;
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2
IS
LC$TRIGGER_STMT VARCHAR2 (4000);
CURSOR LCUR$COLUMNS
IS
SELECT COLUMN_NAME
FROM USER_TAB_COLS
WHERE TABLE_NAME = IN_TABLE_NAME
ORDER BY COLUMN_ID;
BEGIN
LC$TRIGGER_STMT :=
'CREATE OR REPLACE TRIGGER '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG '
|| CHR (10)
|| 'AFTER INSERT OR UPDATE OR DELETE'
|| CHR (10)
|| 'ON '
|| IN_TABLE_NAME
|| ' FOR EACH ROW '
|| CHR (10)
|| 'DECLARE '
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL DML_LOG.GT$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'LC$OPERATION VARCHAR2 (1);'
|| CHR (10)
|| 'PROCEDURE ADD_ELEMENT ('
|| CHR (10)
|| 'IN_OPERATION VARCHAR2,'
|| CHR (10)
|| 'IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,'
|| CHR (10)
|| 'IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,'
|| CHR (10)
|| 'IN_NEW_VALUE LOGGING_DATA_DTL.NEW_VALUE%TYPE)'
|| CHR (10)
|| 'IS'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| ' IF NOT (IN_OPERATION = ''U'' AND IN_NEW_VALUE = IN_OLD_VALUE)'
|| CHR (10)
|| 'THEN'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL.EXTEND;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'END IF;'
|| CHR (10)
|| 'END ADD_ELEMENT;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL := DML_LOG.GT$LOGGING_DATA_DTL ();'
|| CHR (10)
|| 'LC$OPERATION :='
|| CHR (10)
|| 'CASE WHEN INSERTING THEN ''I'' WHEN UPDATING THEN ''U'' ELSE ''D'' END;'
|| CHR (10);
FOR LREC$COLUMNS IN LCUR$COLUMNS
LOOP
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' ADD_ELEMENT (LC$OPERATION,'''
|| LREC$COLUMNS.COLUMN_NAME
|| ''',:OLD.'
|| LREC$COLUMNS.COLUMN_NAME
|| ',:NEW.'
|| LREC$COLUMNS.COLUMN_NAME
|| ');'
|| CHR (10);
END LOOP;
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' DML_LOG.ADD_LOG (LT$LOGGING_DATA_DTL,'''
|| IN_TABLE_NAME
|| ''',:NEW.ROWID,LC$OPERATION);'
|| CHR (10)
|| 'END '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG ;';
RETURN LC$TRIGGER_STMT;
END GENERATE_TRIGGER;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
LC$COLUMNS VARCHAR2 (512) := '';
LC$KEY VARCHAR2 (512);
CURSOR LCUR$COLUMNS (
IN_TABLE_NAME VARCHAR2)
IS
SELECT CON_C.COLUMN_NAME
FROM USER_CONS_COLUMNS CON_C, USER_CONSTRAINTS CON
WHERE CON.CONSTRAINT_NAME = CON_C.CONSTRAINT_NAME
AND CON.CONSTRAINT_TYPE = 'P'
AND CON.TABLE_NAME = IN_TABLE_NAME
ORDER BY POSITION;
BEGIN
FOR LREC$COLUMNS IN LCUR$COLUMNS (IN_TABLE)
LOOP
LC$COLUMNS :=
LC$COLUMNS
|| LREC$COLUMNS.COLUMN_NAME
|| '||'''
|| IN_DELIMETER
|| '''||';
END LOOP;
LC$COLUMNS := RTRIM (LC$COLUMNS, '||''' || IN_DELIMETER || '''||');
EXECUTE IMMEDIATE
'SELECT '
|| LC$COLUMNS
|| ' FROM '
|| IN_TABLE
|| ' WHERE ROWID='''
|| IN_ROWID
|| ''''
INTO LC$KEY;
RETURN LC$KEY;
END GET_COMPOSITE_KEY;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE)
IS
BEGIN
GC$APP_USER := IN_APP_USER;
END SET_APP_USER;
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE
IS
BEGIN
RETURN GC$APP_USER;
END GET_APP_USER;
END DML_LOG;
/
5. Then create this trigger to generate automatically log register trigger.
set serveroutput on
BEGIN
DBMS_OUTPUT.PUT_LINE (DML_LOG.GENERATE_TRIGGER ('DEPARTMENTS'));
END;
/
6. Copy the trigger and paste it in SQL command line for createing this trigger.
7. After that genarate a DML statement.
8. commit the DML.
Thanks. Also check Ghar Baithe Paise Kamane ke Asan Tarike Kya aap bhi online paise kamaana chahte hai. Aaj hum batayge ke kaise ap bhi tested methods ko use kar ke paise kama sakte hain.
ReplyDelete