Database Reference
In-Depth Information
-- Error: primary key violation
insert into @T(ID) values(2),(3),(3)
-- 1 row
select * from @T
As you see in Figure
12-7
, the second insert statement did not add the rows to the table.
Figure 12-7.
Table variables: statement-level rollback
Now let's examine what happens when we rollback an explicit transaction. The code for doing this is shown
in Listing 12-12.
Listing 12-12.
Table variables: explicit transactions
declare
@Errors table
(
RecId int not null,
[Error] nvarchar(512) not null,
primary key(RecId)
)
begin tran
-- Insert error information
insert into @Errors(RecId, [Error])
values
(11,'Price mistake'),
(42,'Insufficient stock')
rollback
/* Do something with errors */
select RecId, [Error] from @Errors
As you see in Figure
12-8
, the explicit rollback statement did not affect the table variable data. You can benefit
from such behavior when you need to collect some error or log information that you want to persist after the
transaction has been rolled back.
Figure 12-8.
Table variables: explicit transactions
