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