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

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

Special Storage Series – III Unrestricted-Length Object Data I

Read Previous Article:

• Special Storage Series – I (Checking the 8K (8192) size of page)
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.B One row can span many row-overflow pages.
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.C Update statement on row-overflow pages data.

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