Saving in transaction a master-detail relation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Saving in transaction a master-detail relation

Author

Topic

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2009-02-12 : 09:46:21

Hi,

I need to save two tables in master-detail relation in the same transaction. dbo.Detail table is linked with dbo.Master table via foreign key. Take a look at the example below:

CREATE TABLE [dbo].[Master](
	[Mas_ID]           [int] IDENTITY(1,1) NOT NULL,
	[Mas_Something]       varchar(50) NOT NULL
	CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED 
	(
		[Mas_ID] ASC
	)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 30) ON [PRIMARY],
) ON [PRIMARY]

CREATE TABLE [dbo].[Detail](
	[Det_ID]           [int] IDENTITY(1,1) NOT NULL,
	[Det_MasID]           [int] NOT NULL,
	[Det_Something]       varchar(50) NOT NULL
	CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED 
	(
		[Det_ID] ASC
	)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 40) ON [PRIMARY],

	CONSTRAINT [FK_Detail_Master] FOREIGN KEY([Det_MasID]) REFERENCES [dbo].[Master] ([Mas_ID])
) ON [PRIMARY]

BEGIN TRANSACTION

DECLARE @MasID int


INSERT INTO dbo.Master([Mas_Something]) VALUES('first item')

SET @MasID = (SELECT TOP 1 Mas_ID FROM dbo.Master ORDER BY Mas_ID DESC)  --how to find the last item inserted to the dbo.Master in this transaction?

INSERT INTO dbo.Detail([Det_MasID], [Det_Something]) VALUES(@MasID, 'first Subitem')

IF (@@ERROR <> 0)
	ROLLBACK TRANSACTION
ELSE
	COMMIT TRANSACTION

SELECT * FROM dbo.Master
SELECT * FROM dbo.Detail

My question is: how to find in optimal way last inserted record into the dbo.Master table?

SET @MasID = (SELECT TOP 1 Mas_ID FROM dbo.Master ORDER BY Mas_ID DESC)

I don't think that this is a proper way of doing this.

Thanks for your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 09:52:23

use SCOPE_IDENTITY()

SET @MasID =SCOPE_IDENTITY()

i assume PK of dbo.Master is an identity column

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 09:59:04

You can also take advantage of the new SQL Server 2005 and later OUTPUT operator!

INSERT	Master
	(
		Mas_Something
	)
OUTPUT	inserted.Mas_ID,
	'Second subitem'
INTO	Detail
VALUES	(
		'Second item'
	)

SELECT * FROM [Master]
SELECT * FROM Detail

Or change your old code to

INSERT INTO @Detail([Det_MasID], [Det_Something]) VALUES(@MasID, 'first Subitem')

IF (@@ERROR <> 0)
	ROLLBACK TRANSACTION
ELSE
	COMMIT TRANSACTION

SELECT * FROM @Master
SELECT * FROM @Detail

INSERT	Master
	(
		Mas_Something
	)
OUTPUT	inserted.Mas_ID,
	'Second subitem'
INTO	Detail
VALUES	(
		'Second item'
	)

SELECT * FROM [Master]
SELECT * FROM Detail

E 12°55'05.63"
N 56°04'39.26"

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2009-02-12 : 10:38:40

Thank you visakh16 and Peso.

Peso, your example doesn't work for me, I am getting error message: "The target table 'Detail' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_Detail_Master'"

Is there a way for me to use OUTPUT when I have a constraint on the target table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources