About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Tuesday, December 31, 2013

Auditing only updated column with Trigger

Read Prev Project: Auditing Update and Delete Operation with Trigger

When you want to Audit only the column which gets updated, you require something called update () function to find the column which gets updated.

I am writing this article because today I earshot something like this from a conversation which was going between an SQL Expert Manish Sharma and a Project team of iGATE. Their discussion influences me to write up this article. Thanks guys n sorry for listening your discussion;-P, anyways your discussion made an increment to my article list. 

Here I have a table emp with following data.

select * from emp
emp_id      ename      dept_id
----------- ---------- -----------
1           neha       1
2           richa      1
3           sidhika    2
4           sapna      2
5           sneha      3
6           Kavita     3
7           Binny      1

(7 row(s) affected)

Now I am creating a Audit table which will hold the old values of update or delete operation.

Create table Tab_MyAudit1
(    
      A_Action char(1) not null,
      A_AuditDate datetime not null,
      A_Table     varchar(15) not null,  
      A_Values xml
)
Command(s) completed successfully.

My Objective: To write a trigger which will insert only the old column values which gets updated with update statement, no need to record other column values which are not the part of update statement. And in case of delete record all column values.

Now here we are creating a trigger which is using Update() function to check which column gets updated, accordingly it insert the values to Audit table.  The if (SELECT COUNT(*) FROM inserted) > 0 will tell whether the DML operation is Update or Delete. In case of Update INSERTED table will have data so this condition will set to true whereas, in case of delete, only Deleted table will hold data, so this condition will set to false and the pointer will go to else part.

create trigger TGR_EMP_UpdateDeleteLog1
ON emp
for UPDATE,DELETE
AS
begin
      Declare @AuditAction varchar(1)
      Declare @OldMessage varchar(200)
     
      --Condtion for U or D
      if (SELECT COUNT(*) FROM inserted) > 0
      begin
            set @AuditAction = 'U'
            set @OldMessage =
(select  (case
            when update(ename)then deleted.ename end) as OldName,
            (case
when update(dept_id)then deleted.dept_id end) as OldDeptId
            from deleted
            inner join emp
            on deleted.emp_id=emp.emp_id
            FOR XML AUTO)
      end
      else
      begin
            set @AuditAction = 'D'
            set @OldMessage =
            (SELECT * from deleted FOR XML AUTO)
      end
      insert into Tab_MyAudit1 values
      (@AuditAction,getdate(),'EMP',@OldMessage)
End
Command(s) completed successfully.

Now our Trigger is ready, let’s test it with DML statement, before that lets query Audit table and emp table.

select * from Tab_MyAudit
(0 row(s) affected)

So there is no row in Audit table Tab_MyAudit for now. Lets query Emp table too

select * from emp
emp_id      ename      dept_id
----------- ---------- -----------
1           neha       1
2           richa      1
3           sidhika    2
4           Pragyan    2
5           sneha      3
6           Kavita     3

(6 row(s) affected)


Update Operation

Now since everything is in place, let’s fire an Update DML where we are updating employee name to Sneha where the employee id is 4.

update emp
set ename = 'Sneha' where emp_id = 4

let’s check Audit table, the Audit table should record only old Name which gets changed through Update statement. i.e “Pragyan” should get inserted in Audit table.

select * from Tab_MyAudit1
A_Action A_AuditDate             A_Table A_Values
-------- ----------------------- ------- ---------------------------
U        2013-12-30 22:11:26.123 EMP     <deleted OldName="Pragyan"/>

(1 row(s) affected)

So to our expectation we found correct entry in Audit table. Let’s query Emp table too to check the update.

select * from emp
emp_id      ename      dept_id
----------- ---------- -----------
1           neha       1
2           richa      1
3           sidhika    2
4           Sneha      2
5           sneha      3
6           Kavita     3

(6 row(s) affected)

Now we have two employee with employee name ‘Sneha’ but different dept_id i.e. 2 and 3 , lets update the dept Id to 1 where employee name is ‘Sneha’

update emp
set dept_id = 1 where ename = 'Sneha'

Now let’s query  the Audit table, our expectation is, the department which gets updated to 1 are 2 and 3, so 2 and 3 should gets inserted in a single row in audit table.

select * from Tab_MyAudit1

A_Action A_AuditDate A_Table A_Values
-------- ----------- ------- ----------------------------------------------
U        2013-12-30  EMP   <deleted OldName="Pragyan" />
U        2013-12-30  EMP   <deleted OldDeptId="2"/><deleted OldDeptId="3" />

(2 row(s) affected)

So to our expectation it records both the department id as we can see the second record. Also let’s query emp table to check the update.

select * from emp
emp_id      ename      dept_id
----------- ---------- -----------
1           neha       1
2           richa      1
3           sidhika    2
4           Sneha      1
5           sneha      1
6           Kavita     3

(6 row(s) affected)

Delete Operation

Now here we are trying to delete employee with employee id 5 and will check whether the whole record values gets stored in Audit Table.

delete emp where emp_id = 5

Let’s query Audit table

select * from Tab_MyAudit1

A_Action A_AuditDate A_Table A_Values
-------- ---------- -------- ---------------------------------------------
U        2013-12-30 EMP    <deleted OldName="Pragyan" />
U        2013-12-30 EMP    <deleted OldDeptId="2"/><deleted OldDeptId="3"/>
D        2013-12-30 EMP    <deleted emp_id="5" ename="sneha" dept_id="1"/>

(3 row(s) affected)

So to our expectation as we can see the third record, it records all column value in Audit table. Lets query our Employee table to see the update.
  
select * from emp
emp_id      ename      dept_id
----------- ---------- -----------
1           neha       1
2           richa      1
3           sidhika    2
4           Sneha      1
6           Kavita     3

(5 row(s) affected)

Take a good look at Programmer, he will take care of your needs, code has answer to every problem.


Post Reference: Vikram Aristocratic Elfin Share

Auditing Update and Delete Operation with Trigger


There are many ways to audit trail, and with the release of SQL Server 2008, we got ample of way to audit your DML statements, but in this article we will see how we can audit any DML (update/Delete) using Trigger. For that we are taking an existing EMP table.

select * from emp
emp_id      ename      dept_id
----------- ---------- -----------
1           neha       1
2           richa      1
3           sidhika    2
4           sapna      2
5           sneha      3
6           Kavita     3
7           Binny      1

(7 row(s) affected)

We are creating a Audit Table which will hold the old values which gets updated or deleted with DML operation on EMP table. Here we have taken an XML field which will hold the old values in XML format.

Create table Tab_MyAudit
(    
      A_Action char(1) not null,
      A_AuditDate datetime not null,
      A_Table     varchar(15) not null,  
      A_Values xml
)
Command(s) completed successfully.

Now since platform is ready we can now create our Trigger to log the data which gets updated or deleted either using Update or Delete statement.

create trigger TGR_EMP_UpdateDeleteLog
on emp
after update, delete
as
begin

      declare @oldValues xml
      set @oldValues = (SELECT *    from  deleted
      FOR XML AUTO)    
     
      insert Tab_MyAudit
      select A_Action = case when i.emp_id is null then 'D' else 'U' end,
      A_AuditDate = GetDate(),'EMP',@oldValues
      from deleted d
           left outer join inserted i
           on i.emp_id = d.emp_id              
end
Command(s) completed successfully.

Here in this trigger we have used LEFT JOIN, left of left join is DELETED table and right if LEFT JOIN is INSERTED table, this is because, whether delete or Update operation perform, DELETED table always have row but INSERTED table will get row only in case of UPDATE statement fire.

Let’s check the data inside Audit Table

select * from Tab_MyAudit
(0 row(s) affected)

So for now we don’t have any records in Audit table. Now it’s time to check our trigger by firing some update statement on EMP table.

update emp
set ename = 'Pragyan' where emp_id = 4

Now since we have fired Update statement, Trigger must have implicitly fired and the old value of ename where emp_id is 4 must saved in Audit Table. So lets query Audit table to check the trail.

select * from Tab_MyAudit
A_Action A_AuditDate             A_Table A_Values
-------- ----------------------- ------- --------------------------------------------------
U        2013-12-30 21:19:03.127 EMP     <deleted emp_id="4" ename="Pragyan" dept_id="2" />

Yes to our believe, the old value of emp_id =4 has been saved in Audit trail Table.

Let’s now try with Delete statement. Here we are deleting Employee with emp_id = 7

delete emp where emp_id = 7

Trigger must have implicitly fired with the Delete statement and the old value of employee with emp_id is 4 must saved in Audit Table. So lets query Audit table to check the trail.

select * from Tab_MyAudit
A_Action A_AuditDate             A_Table A_Values
-------- ----------------------- ------- --------------------------------------------------
U        2013-12-30 21:19:03.127 EMP     <deleted emp_id="4" ename="Pragyan" dept_id="2" />
D        2013-12-30 21:21:02.850 EMP     <deleted emp_id="7" ename="Binny" dept_id="1" />

(2 row(s) affected)

So here we can see in the second record the deleted values are stored in Audit table.

In the next article we will see, how to audit only column values which gets updated using DML statement.

When codes go wild it is hard to handle, subsequently programmer weight comes up.


Post Reference: Vikram Aristocratic Elfin Share

Monday, December 30, 2013

Some finding on INTO Clause with TableVariable, TempVariable and TempTable


I will start with conclusion, there after we will try implementing and checking the conclusion.

On Local Temp Variable
Note: You cannot use Temp Variable in select into clause for the assignment. Use Set or = operator.

On Temp Table
Note: You can use select into clause to insert data from select query to Temp Table. INSERT INTO also work with Temp Variable.

On Table Variable
Note: You cannot use select into clause to insert data from select query to Table Variable, instated use INSERT INTO for insertion.

Let take example to find the conclusion correct.

Temp Variable, here we are declaring Temp Variable and then trying to assign value using into clause in select statement.

declare @row_count int
select count(1) into @row_count  from sys.columns
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@row_count'.

So we can see it throws error. Now let’s assign temp variable with ‘=’ operator.

declare @row_count int
select @row_count = count(1) from sys.columns
select @row_count as [RowCount]
RowCount
-----------
3619

So with ‘=’ operator we can assign temp variable in select statement.  Let’s now try assignment using set operator.

declare @row_count int
set   @row_count = (select count(1)  from sys.columns)
select @row_count  as [RowCount]
RowCount
-----------
3619

Conclusion: Assignment of the temporary variable in select statement can be done through set operator, ‘=’ operator but not through into clause.

Temp Table: Now let’s try with Temp table, here we are declaring Temp Table and then trying to assign value using into clause in select statement.

select count(1) as countRow into #tempRow  from sys.columns
select * from #tempRow
countRow
-----------
3619

Conclusion: Here we can see, we can assign temp table using into clause.  But set and ‘=’ can’t worked out with Temp table since both this is operator is used for variable assignment.

 Table Variable: Here first we will try to insert data through “INTO” clause in the Table Variable, there after we will try with other option

declare @tableVar table (col1 int);
select count(1) as countRow into @tableVar  from sys.columns   
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@tableVar'.
With the output we can see, we cannot do insertion into table variable through “INTO” Clause. Lets try out with Insert statement.

declare @tableVar table (col1 int)
insert into @tableVar
select count(1) from sys.columns

select * from @tableVar
col1
-----------
3619

Insert statement worked out with table variable.
And since it is Table Variable we cannot use set or ‘=’ operator to insert data in Table variable.

Boldly implementation of code logic will get your opposite number duck on your feet.


Post Reference: Vikram Aristocratic Elfin Share

Monday, December 23, 2013

Special Storage Series – III Unrestricted-Length Object Data I


Read Previous Article:

There are mainly three kind of LOB type
  • Text
  • Ntext
  • Image

And if your table consists of any of these LOB type for your field, then by default the actual data is not stored on regular data pages, because for LOB data type SQL Server has separate pages for storing the data. Like row-overflow data, LOB data are stored in their own set of pages.

The Pages where the data of LOB data get stored are
PageType 3, LOB or row-overflow page, TEXT_MIXED
PageType4, LOB or row-overflow page, TEXT_DATA

For LOB column, SQL Server stores a 16 byte pointer in the data row that indicates where the actual data can be found i.e either PageType3 or PageType4. Although default behavior of SQL Server is to store the LOB data off the data row but SQL Server also allows you to change the default behavior of LOB data storage mechanism and allow LOB data to get stored in the data row itself if it is small enough.  

The collection of 8K pages that form the LOB data, not necessary located next to each other, they might be scattered but logically they are organized in a B-Tree Structure, so operation on LOB data type is very efficient.

Let’s take a look at how data of text type i.e. LOB type get stored in separate LOB Page, for this we are creating a table Tab_VKM_LOBTable with col1 as text data type.

create table Tab_VKM_LOBTable
(col1 text)
Command(s) completed successfully.

Let’s insert a record to the table.

insert into Tab_VKM_LOBTable
select REPLICATE('a',3000)
(1 row(s) affected)

Now we will introspect the table storage detail, ie in what all kind of pages, data of table are getting stored by querying System Catalog   sys.partitions and sys.allocation_units.

select OBJECT_NAME(p.object_id) as TableName,
p.partition_id,p.partition_number,p.rows,
au.total_pages as Pages,
au.type_desc as PageType
from sys.partitions p, sys.allocation_units au
where p.partition_id=au.container_id
and p.object_id=OBJECT_ID('Tab_VKM_LOBTable')

TableName        partition_id      partition_number rows Pages PageType
--------------------------------------------------- ----------------------
Tab_VKM_LOBTable 72057594041008128 1                1    2     IN_ROW_DATA
Tab_VKM_LOBTable 72057594041008128 1                1    2     LOB_DATA

Now from the output we can see there are two pages for each Page Type. We can fire DBCC IND query to and see the detail of each pages. Here we are using our custom table which we created in our last post (Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A) and store the output from DBCC IND to the custom table.

Here we are executing DBCC IND for ‘Tab_VKM_LOBTable' and storing the output in out custom made TAB_VKM_TablePages table.

insert into TAB_VKM_TablePages
exec ('dbcc ind (''master'',''Tab_VKM_LOBTable'',-1)')

select PageFID,PagePID,OBJECT_NAME(objectId) as [Table],
iam_chain_type,PageType from TAB_VKM_TablePages

PageFID PagePID Table            iam_chain_type PageType
------- ------------------------ -------------- --------
1       184     Tab_VKM_LOBTable In-row data    10
1       183     Tab_VKM_LOBTable In-row data    1
1       182     Tab_VKM_LOBTable LOB data       10
1       181     Tab_VKM_LOBTable LOB data       3

(4 row(s) affected)

From the output we can observe two pages for LOB Data and two pages for In-row data. The Data page hold a 16byte pointer to LOB Page. The descriptions for page type are

PageType =  1, Data Page
PageType =  2, Index Page
PageType =  3, LOB, Row-Overflow page, TEXT_MIXED
PageType =  4, LOB, Row-Overflow page, TEXT_DATA
PageType =  10, IAM Page

Here we can make out there are one data page and one IAM page for both in-row-data and LOB-data.

It is also possible to store LOB data in in-row data pages; we will see this in coming article from this series.

Code confidently in the course of your logic and other will bow their head to the direction of your code triumph.


Post Reference: Vikram Aristocratic Elfin Share