Subscribe For Free Updates!

We'll not spam mate! We promise.

Aug 21, 2013

Log/Audit Table in Oracle and SQL SERVER

Views:

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 →
FOLLOW US →
SHARE IT →

1 comments:

Become a Fan

visual studio learn