Audit Trail with OUTPUT clause in SQL Server 2005/2008/Denali

AuditTrail is one of the task client asked for in most of the project so thatthey can keep watch on crucial fields regarding its change.

I have seen many small & few medium sized companies isstill not hiring SQL Server professional and used to go ahead with .NETdeveloper. .NET developer generally keep their knowledge updated with .NETtechnology but they lack of knowledge in SQL Server domain so for AuditTrail, they still rely on old pseudo table (INSERTED, DELETED) accessiblein Trigger. But in SQL Server 2005 and later, you don’t even need to rely onTriggers for AuditTrail but you can use new “OUTPUT” clause.

“OUTPUT” clause is still much underutilized feature even after7 years. That is the reason I am writing on Audit trail and OUTPUT clause onceagain.

Within “OUTPUT” clause, you can access pseudo table andgenerate your “AuditTrail” log. I will show you with an example.

We will have one table of Test comes to an environmentalcompany to analyze, if client change the Test s/he wants to perform, we have tokeep trail on that, if he cancel any test, we have to keep trail on that too.

–we will keep”Audit Trail” for below given table

create table ClientOrder

(

      OrderID varchar(5)

      ,ClientID varchar(5)

      ,Test varchar(20)

      ,OrderDate datetime default getdate()

)

GO

–following isthe table, we will keep “Audit Trail” in.

–this will keeptrack of all data changed

CREATE TABLE AuditOfOrder

(

      Id INT Identity(1,1)

      ,OrderID varchar(50)

      ,OldTest varchar(20)

      ,NewTest varchar(20)

      ,DMLPerformed varchar(15)

      ,ChangeDate datetime default getdate()

)

GO

–inserting datain “ClientOrder” table

–all insertwill be stored in audit trail table too via “OUTPUT” clause

INSERT INTO ClientOrder(OrderID,ClientID,Test)

Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

VALUES(‘A1001’,‘CHEM1’,‘VOCMSGroup1’)

GO

–inserting datain “ClientOrder” table

–all insertwill be stored in audit trail table too via “OUTPUT” clause

INSERT INTO ClientOrder(OrderID,ClientID,Test)

Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

VALUES(‘A1001’,‘CHEM1’,‘PesticideGroup1’)

GO

–let us seewhat we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

–we willperform UPDATE on “ClientOrder” table

–which will berecorded in “AuditOfOrder” table too

INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

SELECT

t.OrderID,

t.OldTest,

t.NewTest,

t.DMLPerformed

FROM

(

UPDATEClientOrder

      SET Test =‘SVOC Stars’

OUTPUT

      inserted.OrderID AS OrderID,

      deleted.Test AS OldTest,

      inserted.Test AS NewTest,

      ‘UPDATE’ as DMLPerformed

WHEREClientOrder.Test=‘VOCMS Group1’

) t

GO

–let us seewhat we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

–Finally thelog of Delete will be stored in the same way.

INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

SELECT

t.OrderID,

t.OldTest,

t.NewTest,

t.DMLPerformed

FROM

(

DELETE FROM ClientOrder

OUTPUT

      deleted.OrderID AS OrderID,

      deleted.Test AS OldTest,

      deleted.Test AS NewTest,

      ‘DELETE’ as DMLPerformed

WHEREClientOrder.Test=‘SVOC Stars’

) t

GO

–let us seewhat we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

Isn’t this interesting? Start using it.

BTW, I have previously written some articles regarding AuditTrail techniques (old & new both), if you want to refer it, have a look atbelow links:

http://www.sqlhub.com/2009/11/audit-in-sql-server-2008.html

http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html

http://www.sqlhub.com/2009/03/output-deleted-and-inserted-from-insert.html

http://www.sqlhub.com/2009/08/output-clause-in-sql-server-20082005.html

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Advertisements

Audit Trail with OUTPUT clause in SQL Server 2005/2008/Denali

AuditTrail is one of the task client asked for in most of the project so thatthey can keep watch on crucial fields regarding its change.

I have seen many small & few medium sized companies isstill not hiring SQL Server professional and used to go ahead with .NETdeveloper. .NET developer generally keep their knowledge updated with .NETtechnology but they lack of knowledge in SQL Server domain so for AuditTrail, they still rely on old pseudo table (INSERTED, DELETED) accessiblein Trigger. But in SQL Server 2005 and later, you don’t even need to rely onTriggers for AuditTrail but you can use new “OUTPUT” clause.

“OUTPUT” clause is still much underutilized feature even after7 years. That is the reason I am writing on Audit trail and OUTPUT clause onceagain.

Within “OUTPUT” clause, you can access pseudo table andgenerate your “AuditTrail” log. I will show you with an example.

We will have one table of Test comes to an environmentalcompany to analyze, if client change the Test s/he wants to perform, we have tokeep trail on that, if he cancel any test, we have to keep trail on that too.

–we will keep”Audit Trail” for below given table

create table ClientOrder

(

      OrderID varchar(5)

      ,ClientID varchar(5)

      ,Test varchar(20)

      ,OrderDate datetime default getdate()

)

GO

–following isthe table, we will keep “Audit Trail” in.

–this will keeptrack of all data changed

CREATE TABLE AuditOfOrder

(

      Id INT Identity(1,1)

      ,OrderID varchar(50)

      ,OldTest varchar(20)

      ,NewTest varchar(20)

      ,DMLPerformed varchar(15)

      ,ChangeDate datetime default getdate()

)

GO

–inserting datain “ClientOrder” table

–all insertwill be stored in audit trail table too via “OUTPUT” clause

INSERT INTO ClientOrder(OrderID,ClientID,Test)

Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

VALUES(‘A1001’,‘CHEM1’,‘VOCMSGroup1’)

GO

–inserting datain “ClientOrder” table

–all insertwill be stored in audit trail table too via “OUTPUT” clause

INSERT INTO ClientOrder(OrderID,ClientID,Test)

Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

VALUES(‘A1001’,‘CHEM1’,‘PesticideGroup1’)

GO

–let us seewhat we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

–we willperform UPDATE on “ClientOrder” table

–which will berecorded in “AuditOfOrder” table too

INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

SELECT

t.OrderID,

t.OldTest,

t.NewTest,

t.DMLPerformed

FROM

(

UPDATEClientOrder

      SET Test =‘SVOC Stars’

OUTPUT

      inserted.OrderID AS OrderID,

      deleted.Test AS OldTest,

      inserted.Test AS NewTest,

      ‘UPDATE’ as DMLPerformed

WHEREClientOrder.Test=‘VOCMS Group1’

) t

GO

–let us seewhat we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

–Finally thelog of Delete will be stored in the same way.

INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

SELECT

t.OrderID,

t.OldTest,

t.NewTest,

t.DMLPerformed

FROM

(

DELETE FROM ClientOrder

OUTPUT

      deleted.OrderID AS OrderID,

      deleted.Test AS OldTest,

      deleted.Test AS NewTest,

      ‘DELETE’ as DMLPerformed

WHEREClientOrder.Test=‘SVOC Stars’

) t

GO

–let us seewhat we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

Isn’t this interesting? Start using it.

BTW, I have previously written some articles regarding AuditTrail techniques (old & new both), if you want to refer it, have a look atbelow links:

http://www.sqlhub.com/2009/11/audit-in-sql-server-2008.html

http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html

http://www.sqlhub.com/2009/03/output-deleted-and-inserted-from-insert.html

http://www.sqlhub.com/2009/08/output-clause-in-sql-server-20082005.html

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT Clause in SQL Server 2008/2005

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.

You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.

Let us see its usefulness by one small example.

–create table for demo and insert few records

create table testOutPut(tid int identity(1,1), name varchar(50))

insert into testOutPut (name)

select ‘Ritesh’ union all

select ‘Rajan’ union all

select ‘Pinal’ union all

select ‘Bihag’ union all

select ‘John’ union all

select ‘Bhaumik’ union all

select ‘Avi’ union all

select ‘James’

go

–check whether all records came with proper tid

select * from testOutPut

go

–create temp duplicate table which will store data which are deleted

create table #deleted (id int, name varchar(50))

–deleting records from testOutPut table and inserting those deleted

–records into temp table with help of OUTPUT clause and DELETED pseudo table

delete testOutPut

output DELETED.* into #deleted

where tid < 3

–checking temp table

select * from #deleted

–this will show deleted data on screen

–but won’t store anywhere like we did in previous snippet

–and stored data in #deleted (temp table)

delete testOutPut

output DELETED.*

where tid >3

select * from testOutPut

go

select * from #deleted

go

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

BSA (Body Surface Area) calculation in stored procedure with OUTPUT parameter in Microsoft SQL Server 2005:

Herewith, I am giving you one more example of OUTPUT parameter in Stored Procedure for calculating BSA (Body Surface Area). You are in development of medical software than BSA is not a new term for you. If your body surface are is between 1 to 2 than it is normal as per my little knowledge about BSA. I am giving a sample example which will calculate BSA based on the given height and weight. Height and Weight should be either in kg (weight) and cm (height) or in lbs (weight) and inch (height). You can make it more customize by giving more dynamic conversions.

–CREATING stored procedure to return BSA (Body Surface Area)

–The calculation is from the formula of DuBois and DuBois:

–BSA = (W 0.425 x H 0.725) x 0.007184

–where the weight is in kilograms and the height is in centimeters.

—

—

–DuBois D, DuBois EF. A formula to estimate the approximate surface area

–if height and weight be known. Arch Intern Medicine. 1916; 17:863-71.

–Wang Y, Moss J, Thisted R. Predictors of body surface area.

CREATE PROC CalcBSA

@option INT,

@weight FLOAT,

@height FLOAT,

@bsa FLOAT OUTPUT

AS

SET NOCOUNT ON

–if weight and height are in kg and cm accordingly

IF @option=1

BEGIN

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

–if weight and height are in lbs and inch accordigly

ELSE

BEGIN

SET @weight=(@weight/2.2046)

SET @height=@height*2.54

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

GO

–once you done with creating stored procedure, let us see whether actually it works!!!!

DECLARE @BSA FLOAT

EXECUTE calcbsa 1,84,180,@BSA OUTPUT

PRINT @BSA

GO

If you are new to stored procedure and wants to study it than do have a look at my following basic articles. Those articles contain from basic definition of stored procedure to different usage of SP.

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

http://www.sqlhub.com/2009/03/return-data-with-output-parameter-from.html

http://www.sqlhub.com/2009/03/dml-insert-with-multiple-ways-in-sql.html

http://www.sqlhub.com/2009/03/delete-many-multiple-records-in-bunch.html

http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

http://www.sqlhub.com/2009/03/create-your-own-system-stored-procedure.html

Reference: Ritesh Shah
http://www.SQLHub.Com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Return data with OUTPUT parameter from Stored Procedure in SQL Server 2005

Herewith, I am keeping my promise and moving ahead with topic of stored procedure. I will be explaining how to return data from stored procedure in SQL Server with OUTPUT parameter. If you are new to Stored Procedure than I kindly advice you to move to my prior article about stored procedure at:

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

When you have need to return data to the calling procedure, you should use of OUTPUT parameter of SQL Server in Stored Procedure.

If you are returning record set for single value, I strongly insist to use OUTPUT parameter as it is much much faster than returning the value.

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating stored procedure which will return data with OUTPUT parameter

CREATE PROC getEmpDeptbyEmpName(@EmpName VARCHAR(50),@EmpDept VARCHAR(10) OUTPUT)

AS

SELECT @EmpDept=dept FROM emps WHERE Name=@EmpName

GO

–calling SP and catching return value in @EmpDept

DECLARE @EmpDept VARCHAR(50)

EXECUTE getEmpDeptbyEmpName ‘Ritesh’, @EmpDept OUTPUT

SELECT @EmpDept AS ‘Department’

GO

Reference: Ritesh Shah
http://WWW.SQLHub.Com
Note: Microsoft Book online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT (Deleted and Inserted) from insert, delete and update statement in SQL-Server 2005 (kind of pseudo table of Trigger):

You may remember pseudo table in trigger from which we can get manipulated data. There was no way out to access that pseudo table outside the trigger before Microsoft SQL Server 2005. The same concept you can get outside of trigger as well in Microsoft SQL Server 2005.

Let us create one table for demonstration

–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

–insert records

Insert into Car (CarName,CarDesc) values (‘Honda Pilot’,‘SUV’)

Insert into Car values (‘Honda CRV’,‘SUV’)

Return data from INSERT statement with INSERTED table

Insert into Car

OUTPUT INSERTED.* –this statement will return all the field of CAR table

— from INSERTED table

values (‘Honda CRV’,‘SUV’)

GO

Return data from DELETE statement WITH DELETED table

DELETE FROM CAR

OUTPUT DELETED.* –this statement will return all records

–which are just deleted based on where condition

where ID=1
GO

Return data from UPDATE statement from INSERTED and DELETED table.

UPDATE CAR SET CarDesc=‘Luxury car’

OUTPUT DELETED.CarDesc as ‘Old Value’, INSERTED.CarDesc as ‘New Value’

WHERE ID=2
GO

Enjoy the power of Microsoft SQL Server 2005

Happy SQLing!!!!!

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah