use pubs
go
if exists (SELECT * FROM sysobjects where name='c' and type='U')
DROP TABLE c
go
if exists (SELECT * FROM sysobjects where name='b' and type='U')
DROP TABLE b
go
if exists (SELECT * FROM sysobjects where name='a' and type='U')
DROP TABLE a
go
if exists (SELECT * FROM sysobjects where name='test' and type='P')
DROP PROC test
go
create table a (
a char(1) primary key )
create table b (
b char(1) references a)
create table c (
c char(1))
go
create proc test as
begin transaction
insert c values ( 'X' )
Insert b values ( 'X' ) -- Fails reference
commit transaction
go
exec test
go
select *
from c
-- Returns `X' !!
-- clear the row from c:
delete c
go
-- Now here is a better formulation for the procedure:
drop proc test
go
create proc test as
begin transaction
insert c values ( 'X' )
IF (@@error <> 0) GOTO on_error
Insert b values ( 'X' ) -- Fails reference
IF (@@error <> 0) GOTO on_error
commit transaction
return (0)
on_error:
rollback transaction
return (1)
go
declare @retcode int
exec @retcode=test
select @retcode
select *
from c
-- clear the row from c:
delete c
go
-- Here is an alternative way to write the proc, to
-- abort if there is any error, without explictly
-- checking for errors.
--
drop proc test
go
create proc test as
SET XACT_ABORT ON
begin transaction
insert c values ( 'X' )
Insert b values ( 'X' ) -- Fails reference
commit transaction
go
exec test
go
select *
from c
