February 26, 2010 at 7:39 am
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 to capture trnxid in tripH, so 2nd and 3rd statement can use this value to perform 2nd and 3rd statement?
So far, i've 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
But the result unsuccessful as follow,
(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'.
🙁
February 26, 2010 at 7:49 am
Hi,
you have switched the columns in the insert statements, change to:
insert into tripD( tripH_trnxid, wday) values(@ID, '1011010')
insert into tripD( tripH_trnxid, wday) values(@ID , '1111110')
Then everything will work as expected.
/Markus
February 26, 2010 at 8:27 am
oh. my brain problem actually. he..he
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply