Need help to insert value into 3 table

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)

Need help to insert value into 3 table

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-26 : 05:45:35

My table design as follow,


CREATE TABLE [dbo].[tripH](
	[trnxid] [int] IDENTITY(1,1) NOT NULL,
	[busn] [varchar](50) NULL,
 CONSTRAINT [PK_tripH] PRIMARY KEY CLUSTERED 
(
	[trnxid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tripD](
	[trnxid] [int] IDENTITY(1,1) NOT NULL,
	[tripH_trnxid] [int] NOT NULL,
	[wday] [char](7) NOT NULL,
 CONSTRAINT [PK_tripD] PRIMARY KEY CLUSTERED 
(
	[trnxid] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tripD]  WITH CHECK ADD  CONSTRAINT [FK_tripD_tripH_trnxid] FOREIGN KEY([tripH_trnxid])
REFERENCES [dbo].[tripH] ([trnxid])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tripD] CHECK CONSTRAINT [FK_tripD_tripH_trnxid]

1st, I want to insert value into tripH. So, the insert statement as insert into tripH(Busn) values('wkm1925')
2nd, I want to insert value into tripD. So, the insert statment as insert into tripD(wday) values('1011010')
3rd, I want to insert value into tripD. So, the insert statment as insert into tripD(wday) values('1111110')

My question is,
1. 1st, 2nd, and 3rd is a single transaction (single workable unit)
2. trnxid in tripH is automatically generated
3. How 2nd, and 3rd get the trnxid from tripH, and this value will be used by 2nd, and 3rd statement?

Need help.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 06:32:56

This might be the solution

DECLARE @ID int
insert into tripH(Busn) values('wkm1925')
@ID = SCOPE_IDENTITY()
insert into tripD(wday, tripH_trnxid ) values(@ID, '1011010')
insert into tripD(wday, tripH_trnxid ) values(@ID , '1111110')

Vabhav T

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-26 : 07:23:39

quote:
Originally posted by vaibhavktiwari83

This might be the solution

DECLARE @ID int
insert into tripH(Busn) values('wkm1925')
@ID = SCOPE_IDENTITY()
insert into tripD(wday, tripH_trnxid ) values(@ID, '1011010')
insert into tripD(wday, tripH_trnxid ) values(@ID , '1111110')

Vabhav T

Me using your code and some customise as follow


BEGIN TRY
	begin transaction
	DECLARE @ID int
	insert into tripH(Busn) values('wkm1925')
	set @ID = SCOPE_IDENTITY()
	insert into tripD(wday, tripH_trnxid ) values(@ID, '1011010')
	insert into tripD(wday, tripH_trnxid ) values(@ID , '1111110')
	commit
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

The result was,
(1 row(s) affected)

(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 20
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tripD_tripH_trnxid". The conflict occurred in database "ETICKETING", table "dbo.tripH", column 'trnxid'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:32:19

that means you do have a fk relationship from tripH_trnxid column to trnxid column of dbo.tripH table so value inserted should be a valid value existing in dbo.tripH

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 09:17:53

means every time it will happen or might be...

because when i am inserting in the table at that time only i m inserting in the another table so there is not any option for mismatch

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 09:22:44

quote:
Originally posted by vaibhavktiwari83

means every time it will happen or might be...

because when i am inserting in the table at that time only i m inserting in the another table so there is not any option for mismatch

Vabhav T

ok. but in that case you need to make sure you're capturing the value generated in first insert and then using it for second insert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-26 : 09:28:08

so, how to adjust my code? or my table design is crazy

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-26 : 10:29:04

i have switched the columns in the insert statements

BEGIN TRY
begin transaction
DECLARE @ID int
insert into tripH(Busn) values('wkm1925')
set @ID = SCOPE_IDENTITY()
insert into tripD(tripH_trnxid,wday) values(@ID, '1011010')
insert into tripD(tripH_trnxid,wday) values(@ID , '1111110')
commit
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:39:58

does the last query work fine?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-26 : 23:50:20

quote:
Originally posted by Delinda

i have switched the columns in the insert statements

BEGIN TRY
begin transaction
DECLARE @ID int
insert into tripH(Busn) values('wkm1925')
set @ID = SCOPE_IDENTITY()
insert into tripD(tripH_trnxid,wday) values(@ID, '1011010')
insert into tripD(tripH_trnxid,wday) values(@ID , '1111110')
commit
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Yes, Mr Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:52:56

good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources