gistfile1.txt

-- Create a table with a ref to itself

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[RefToSelf] [int] NULL,
	[SomeText] [nvarchar](100) NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable]  WITH CHECK ADD  CONSTRAINT [FK_TestTable_TestTable] FOREIGN KEY([RefToSelf])
REFERENCES [dbo].[TestTable] ([Id])
GO

ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_TestTable_TestTable]
GO


-- Now insert some initial data
TRUNCATE TABLE TestTable
INSERT INTO TestTable(SomeText, RefToSelf) VALUES('Inserted', NULL)

-- Validate data
SELECT 'Now selecting content, expecting "inserted" to be the text'
SELECT * FROM TestTable


-- Run a transaction which one would expect to be rolled back as a transaction signals 'all-or-nothing'
BEGIN TRAN

UPDATE TestTable SET SomeText = 'All updated text (only one row)'
INSERT INTO TestTable(SomeText, RefToSelf) VALUES('New inserted with invalid ref', -1)

COMMIT TRAN

-- Now select the content
SELECT 'Now selecting content, expecting "inserted" to be the text'
SELECT * FROM TestTable