SET XACT_ABORT, to rolls back current transaction on Run-Time Error

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

To understand more on it we will do small practical, here we are creating two tables – table1 and table2 with primary key in first table as Parent Table and Foreign Key in second table as Child table.

create table table1

    (col1 int not null primary key);

create table table2

    (col1 int not null references table1(col1));

Go

Command(s) completed successfully.

Our table is ready, now lets insert some records in table 1, which has primary key on col1.

insert into table1 values (1);

insert into table1 values (3);

insert into table1 values (4);

insert into table1 values (6);

go

Now we are setting xact_abort to  off and inserting three records in child table table2 which is referenced by table1 primary key column.

set xact_abort off;

go

begin transaction;

insert into table2 values (1);

insert into table2 values (2); -- foreign key violation.

insert into table2 values (3);

commit transaction;

go

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table2__col1__571DF1D5". The conflict occurred in database "Sparsh", table "dbo.table1", column 'col1'.

The statement has been terminated.

(1 row(s) affected)

select * from table2

col1

-----------

1

3

(2 row(s) affected)

In the above insertion the seocnd records which we were inserting was violating the Integrity constrain, since we were trying to insert value 2 which is not present in col1 of table1.

Eventhough the second insertion fails, it didn’t stop execution there and proceeds with the third row insertion and finally commited the 1 and 2 rows insertion implicitly. This is what happends due to xact_abort to  off.

   

Conclusion: SELECT shows only keys 1 and 3 added.  Key 2 insert failed and was rolled back, but XACT_ABORT was OFF and rest of transaction succeeded.

Now lets set xact_abort to  ON and tries to insert some good and bad records.

set xact_abort on;

go

Command(s) completed successfully.

begin transaction;

insert into table2 values (4);

insert into table2 values (5); -- foreign key violation.

insert into table2 values (6);

commit transaction;

go

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table2__col1__571DF1D5". The conflict occurred in database "Sparsh", table "dbo.table1", column 'col1'.

select * from table2

col1

-----------

1

3

(2 row(s) affected)

Here from the output we can see none of the records gets inserted to the table even though the first and third records comply with the Integrity constrain. This is because, on the failure of the insertion of 2 records which was violating the integrity, the complete transaction rollback.

Conclusion: Key 5 insert error with XACT_ABORT ON caused  all of the second transaction to roll back.

Note: The alternative to this is TRY CATCH block

Take up a piece of code, blend it with ur logic, think dream n live it, let ur every part full of that logic, leave everything aside.. This is how Programmer lives their life. WILL U MARRY ME J  

Post Reference: Vikram Aristocratic Elfin Share

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