Log/Audit Table in Oracle and SQL SERVER

Today i will explain you Why we  create Log/Audit table, How it help us in various situations and How to create Log table and audit table.

In this tutorial we will Learn  How to create Trigger  in Oracle and Sql server.

In this article i uses both data base Oracle and SQL Server.

By creating log tables, data base automatically logging of changes to several of our database tables.






What is propose of creating Log/Audit table
- It Automatically Log all Changes of Database Table.
- It All  Record  of table either it is deleted or update by Some also Log.
- It also show which user insert, update or delete a particular record by Saving his System IP address,system name ,date time and etc.
- We don't write any extra code for it just create a trigger on trageted table this trigger log it's all Information.

How it help
- It help us when some one who's unauthorized had updated ,deleted records we can track it from it IP which is Log in Log/audit table.
- It help in finding a row is Updated by some one on which time, with it previous and updated both rows.
- It help us in Auditing of records.
- It help us in tracking of records.

 How to create Log table and audit table
Lets Suppose we have a table name COMMENT having following Structure
 Script for both Oracle ,SQL Server.

CREATE TABLE "COMMENT" (
  "COMMENT_ID" NUMBER(10,0) NOT NULL,
  "COMMENT_BODY" VARCHAR2(1000),
  "USER" VARCHAR2(100),
  "CREATED_DATE" DATE,
  CONSTRAINT "PK_COMMENT" PRIMARY KEY ("COMMENT_ID")
);

Now we create an other table Name COMMENT_LOG or COMMENT_AUDIT .It is exactly the same table as COMMENT but have few more Columns

Script Works for Both SQL SERVER and Oracle

CREATE TABLE "COMMENT_AUDIT" (
  "COMMENT_ID" NUMBER(10,0) NOT NULL,
  "COMMENT_BODY" VARCHAR2(1000),
  "USER" VARCHAR2(100),
  "CREATED_DATE" DATE,
  "AUDIT_DATE" DATE DEFAULT SYSTIMESTAMP NOT NULL,
  "EVENT" VARCHAR2(10 BYTE),
  "USER_IP_ADDRESS" VARCHAR2(15 BYTE),
  "USER_OS_USER" VARCHAR2(30 BYTE),
  "USER_HOST_NAME" VARCHAR2(30 BYTE),
  "DB_CURRENT_USER" VARCHAR2(30 BYTE)
);

Now Finally, we create the trigger to keep a log of changes to our original table.
Below script on loy work on Oracle

CREATE OR REPLACE TRIGGER "T_COMMENT"
before update or delete on COMMENT for each row
begin
IF INSERTING
   THEN
      

      INSERT INTO comment_audit(
                COMMENT_ID,
      COMMENT_BODY,
      USER,
      CREATED_DATE,
      EVENT,
      USER_IP_ADDRESS,
      USER_OS_USER,
      USER_HOST_NAME,
      DB_CURRENT_USER)
)
           VALUES ( :old.COMMENT_ID,
      :old.COMMENT_BODY,
      :old.USER,
      :old.CREATED_DATE,
     'INSERT',
                   SYS_CONTEXT ('userenv', 'ip_address'),
                   SYS_CONTEXT ('userenv', 'os_user'),
                   SYS_CONTEXT ('userenv', 'host'),
                   SYS_CONTEXT ('userenv', 'current_user'));

   END IF;

 IF DELETING
   THEN
      INSERT INTO comment_audit(
                COMMENT_ID,
      COMMENT_BODY,
      USER,
      CREATED_DATE,
      EVENT,
      USER_IP_ADDRESS,
      USER_OS_USER,
      USER_HOST_NAME,
      DB_CURRENT_USER)
           VALUES ( :old.COMMENT_ID,
      :old.COMMENT_BODY,
      :old.USER,
      :old.CREATED_DATE,
     'DELETE',
                   SYS_CONTEXT ('userenv', 'ip_address'),
                   SYS_CONTEXT ('userenv', 'os_user'),
                   SYS_CONTEXT ('userenv', 'host'),
                   SYS_CONTEXT ('userenv', 'current_user'));
   END IF;


   IF UPDATING
   THEN
      INSERT INTO comment_audit(
                COMMENT_ID,
      COMMENT_BODY,
      USER,
      CREATED_DATE,
      EVENT,
      USER_IP_ADDRESS,
      USER_OS_USER,
      USER_HOST_NAME,
      DB_CURRENT_USER)
           VALUES ( :old.COMMENT_ID,
      :old.COMMENT_BODY,
      :old.USER,
      :old.CREATED_DATE,
     'UPDATE',
                   SYS_CONTEXT ('userenv', 'ip_address'),
                   SYS_CONTEXT ('userenv', 'os_user'),
                   SYS_CONTEXT ('userenv', 'host'),
                   SYS_CONTEXT ('userenv', 'current_user'));

  
   END IF;


end;
/
ALTER TRIGGER "TA_COMMENT" ENABLE;

Now Following Script for SQL Server For Creating a Log/Audit table.

--Function Which Return Client Ip Adrress
CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);
 
    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;
 
    Return @IP_Address;
END



create table Derived_Values
  (
    BusinessUnit nvarchar(100) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)
    ,Time datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP
    )

go

ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);
--Here is Log/Audit Table
create table Derived_Values_Test
  (
    BusinessUnit nvarchar(150)
    ,Questions nvarchar(100)
    ,Answer nvarchar(100)
    ,Time datetime
    ,AuditTime datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP
    ,Host_IP varchar(255)
    ,Current_User varchar(255)
    ,Workstation nchar(30)  NOT NULL DEFAULT HOST_NAME()
    )

go

CREATE TRIGGER trgAfterUpdate ON  [Derived_Values]
FOR UPDATE
AS  
begin
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Updated Record -- After Update Trigger.'

    insert into 
        [Derived_Values_Test]
        (BusinessUnit,Questions, Answer,Time,Host_IP,Current_User) 
    SELECT 
        @BusinessUnit + i.BusinessUnit, i.Questions, i.Answer,i.Time,[dbo].GetCurrentIP(),DB_NAME()
    FROM 
        inserted i
        inner join deleted d on i.BusinessUnit = d.BusinessUnit
end

go

CREATE TRIGGER trgAfterDelete ON  [Derived_Values]
FOR UPDATE
AS  
begin
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Deleted Record -- After Delete Trigger.'

    insert into 
        [Derived_Values_Test]
       (BusinessUnit,Questions, Answer,Time,Host_IP,Current_User) 
    SELECT 
        @BusinessUnit + d.BusinessUnit, d.Questions, d.Answer,d.Time,[dbo].GetCurrentIP(),DB_NAME()
    FROM 
        deleted d
end

go
--Insert Some Record
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q11', 'A11')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q12', 'A12')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q21', 'A21')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q22', 'A22')

UPDATE Derived_Values SET Answer='Updated Answers A11' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q11');
UPDATE Derived_Values SET Answer='Updated Answers A12' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q12');
UPDATE Derived_Values SET Answer='Updated Answers A21' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q21');
UPDATE Derived_Values SET Answer='Updated Answers A22' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q22');

delete Derived_Values;

Plese Feel Free to Socializer This Post

SOCIALIZE IT →

Tweet

FOLLOW US →

SHARE IT →