Trigger for Audit Trail in Oracle

 For the Audit trail of a transactional or master data table, we can use the following format to trace the differences. 

First, create a table for storing audit data named as <SCHEMA_NAME>.<TABLE_NAME>_AUDIT


CREATE TABLE <SCHEMA_NAME>.<TABLE_NAME>_AUDIT (

        COLUMN_NAME TYPE,

        ACTION CHAR(1),

        ACTION_DATE DATE,

        ACTION_USER_ID NUMBER(18),

        CLIENT_DEVICE VARCHAR2(100),

         CLIENT_OS_USER VARCHAR2(100)

);


Then, create a trigger named as <TABLE_NAME>_TRIGGER


CREATE OR REPLACE TRIGGER <TABLE_NAME>_TRIGGER

AFTER INSERT OR UPDATE OR  DELETE  ON <TABLE_NAME> FOR EACH ROW

DECLARE

         V_USER_ID NUMBER(9);

         V_CLIENT_DEVICE VARCHAR2(100);

         V_CLIENT_OS_USER VARCHAR2(100);

BEGIN

         SELECT

                SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'),

                SYS_CONTEXT('USERENV','HOST'),

                SYS_CONTEXT('USERENV','OS_USER')

        INTO

                V_USER_ID,

                V_CLIENT_DEVICE,

                V_CLIENT_OS_USER

        FROM

                DUAL;

               

         CASE

                 WHEN INSERTING THEN

                         INSERT INTO <SCHEMA_NAME>.<TABLE_NAME>_AUDIT(

                                COLUMN_NAME,

                                ACTION,

                                ACTION_DATE,

                                ACTION_USER_ID,

                                CLIENT_DEVICE,

                                CLIENT_OS_USER

                        )

                         VALUES(

                                :NEW.COLUMN_NAME,

                                'I',

                                SYSDATE,

                                V_USER_ID,

                                V_CLIENT_DEVICE,

                                V_CLIENT_OS_USER

                        );

        

                 WHEN UPDATING THEN

                        INSERT INTO <SCHEMA_NAME>.<TABLE_NAME>_AUDIT(

                                COLUMN_NAME,

                                ACTION,

                                ACTION_DATE,

                                ACTION_USER_ID,

                                CLIENT_DEVICE,

                                CLIENT_OS_USER

                        )

                        VALUES(

                               :OLD.COLUMN_NAME,

                                'U',

                                SYSDATE,

                                V_USER_ID,

                                V_CLIENT_DEVICE,

                                V_CLIENT_OS_USER

                        );

        

                 WHEN DELETING THEN

                        INSERT INTO <SCHEMA_NAME>.<TABLE_NAME>_AUDIT(

                                COLUMN_NAME,

                                ACTION,

                                ACTION_DATE,

                                ACTION_USER_ID,

                                CLIENT_DEVICE,

                                CLIENT_OS_USER

                        )

                        VALUES(

                                :OLD.COLUMN_NAME,

                                'D',

                                SYSDATE,

                                V_USER_ID,

                                V_CLIENT_DEVICE,

                                V_CLIENT_OS_USER

                        );

       

        END CASE;

END;


Comments

New comments are not allowed.

Popular posts from this blog

How to Build WPF project with Setup file (Installer Project)

Image
Firstly create a WPF project say "SampleWpfApplication". Here I'm going to use Visual Studio 2013. Created a project named SampleWpfApplication You can add your necessary runtime projects' library dll into your project reference if you want. Here I included a sample runtime project's library into my project. Create Installer Project Now its time to add our installer project. By default in your visual studio you will not see "Visual Studio Installer". If you don't see then you have to download it from visual studio installer project for 2013 and install it. So next You have to create Setup project. Your project is added into your solution. Modify Installer Project Go to view File System of the installer project. Then you'll see  Add "Program Files Folder" Add Project Output 1# Firstly you have to add project output into "Application Folder"
Read more

Build shared library of ffmpeg with x264 to use in visual studio win32 project

After few painful failure attempts at last i was able to build shared library which can be used in visual studio win32 project. Here is the process which i have gone through. Hope this will be helpful. I don't want any body to face the same failure moments.. Environment settings Download msys2 32 bit form this link http://msys2.github.io and install it.(in my case installed " msys2-i686-20160205.exe " in directory is "c:\msys32" ). Run it and run below commands update-core ( Update the system packages with) close MSYS2, run it again and run below commands pacman -Sy pacman -Su  (sometimes this command creates error then you have uninstall msys2 and install again. In that case you can ignore this command) pacman -S pkg-config make diffutils  go to C:\msys32\usr\bin and rename "link.exe" to "link_backup.exe". Because we will not use this link. we will use visual studio link. Download yasm win32 ".exe&quo
Read more

Comma-separated string to tabular format

  Comma-separated string to tabular data Sometimes we need to extract individual data from the comma-separated strings. We can use the following query to do that in oracle. SELECT REGEXP_SUBSTR('my comma separated string', '[^,]', 1, LEVEL) TYPE, LEVEL FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT('my  comma separated string','[^,]') ;
Read more