July 6, 2010 at 12:30 pm
I have two tables that are in a one to many relationship. For design purposes, I want to present my users with a continuous form in Access that shows the joined records between the two and allow them to make updates. It is perfectly fine that the parent record may be represented multiple times as in.
Trailer Load Date Type
10075 5/10/2010 Freight
10075 5/10/2010 Supplies
10076 5/10/2010 Freight
When there is only 1 child record, I'm able to update either the parent or the child. However, when there are 2 child records, I'm getting the error
'Single-row update/delete affected more than one row of a linked table. Unique index contains duplication values.' from Access. The SQL Server equivalent is The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (3 rows).
Is it possible to work around this?
Once the view below is created, update tblTrailerUtilzationDetails_lngTrailerLoadTypeId from '1' to '2' via the view in Management Studio.
CREATE TABLE [dbo].[tblTrailerActivityHeaders](
[lngTrailerActivityHeaderId] [int] IDENTITY(1000,1) NOT NULL,
[txtTrailerDOTNumber] [nvarchar](15) NOT NULL,
[dteTrailerLoadDate] [datetime] NOT NULL,
[lngTrailerLoadLocation] [int] NULL,
[lngTrailerLoadStatus] [tinyint] NULL,
[dteTrailerSealDate] [datetime] NULL,
[txtTrailerSealId] [nvarchar](50) NULL,
[txtTrailerSealNumber] [int] NULL,
[dteTrailerDispatchDate] [datetime] NULL,
[lngTrailerDispatchLocation] [int] NULL,
[lngTrailerDispatchStatus] [tinyint] NULL,
[txtTrailerDispatchUserId] [nvarchar](50) NULL,
[dteTrailerUnloadDate] [datetime] NULL,
[lngTrailerUnloadLocation] [int] NULL,
[lngTrailerUnloadStatus] [tinyint] NULL,
[dblControlId] [float] NULL,
[lngTrailerDeliveryZoneId] [int] NULL,
[dteScheduledUnloadDateTime] [datetime] NULL,
[txtShowNumber] [nvarchar](8) NULL,
[lngTrailerLoadTypeId] [tinyint] NOT NULL,
[txtEntryUserId] [nvarchar](25) NULL,
[dteEntryDateTime] [datetime] NULL,
[txtModifiedUserId] [nvarchar](25) NULL,
[dteModifiedDateTime] [datetime] NULL,
[timestamp] [timestamp] NULL,
[intUsageTypeId] [tinyint] NOT NULL,
CONSTRAINT [PK_tblTrailerActivityHeaders] PRIMARY KEY CLUSTERED
(
[lngTrailerActivityHeaderId] 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].[tblTrailerActivityHeaders] ADD DEFAULT ((0)) FOR [intUsageTypeId]
GO
/****** Object: Table [dbo].[tblTrailerUtilizationDetails] Script Date: 07/06/2010 14:18:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTrailerUtilizationDetails](
[lngTrailerUtilizationDetailsId] [int] IDENTITY(1000,1) NOT NULL,
[lngTrailerActivityHeaderId] [int] NOT NULL,
[lngTrailerLoadTypeId] [tinyint] NOT NULL,
[txtShowNumber] [nvarchar](8) NULL,
[lngContactId] [int] NOT NULL,
[lngClientId] [int] NOT NULL,
CONSTRAINT [PK_tblTrailerLoadUtilizationDetails] PRIMARY KEY CLUSTERED
(
[lngTrailerUtilizationDetailsId] 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].[tblTrailerUtilizationDetails] ADD DEFAULT ((0)) FOR [lngContactId]
GO
ALTER TABLE [dbo].[tblTrailerUtilizationDetails] ADD DEFAULT ((0)) FOR [lngClientId]
GO
/****** Object: View [dbo].[vw_TrailerActivityHeaders_withUtilizationDetails] Script Date: 07/06/2010 14:18:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_TrailerActivityHeaders_withUtilizationDetails]
AS
SELECT dbo.tblTrailerActivityHeaders.lngTrailerActivityHeaderId, dbo.tblTrailerActivityHeaders.txtTrailerDOTNumber,
dbo.tblTrailerActivityHeaders.dteTrailerLoadDate, dbo.tblTrailerActivityHeaders.lngTrailerLoadLocation,
dbo.tblTrailerActivityHeaders.lngTrailerLoadStatus, dbo.tblTrailerActivityHeaders.dteTrailerSealDate, dbo.tblTrailerActivityHeaders.txtTrailerSealId,
dbo.tblTrailerActivityHeaders.txtTrailerSealNumber, dbo.tblTrailerActivityHeaders.dteTrailerDispatchDate,
dbo.tblTrailerActivityHeaders.lngTrailerDispatchLocation, dbo.tblTrailerActivityHeaders.lngTrailerDispatchStatus,
dbo.tblTrailerActivityHeaders.intUsageTypeId, dbo.tblTrailerActivityHeaders.timestamp, dbo.tblTrailerActivityHeaders.dteModifiedDateTime,
dbo.tblTrailerActivityHeaders.txtModifiedUserId, dbo.tblTrailerActivityHeaders.txtEntryUserId, dbo.tblTrailerActivityHeaders.dteEntryDateTime,
dbo.tblTrailerActivityHeaders.dteScheduledUnloadDateTime, dbo.tblTrailerActivityHeaders.lngTrailerDeliveryZoneId,
dbo.tblTrailerActivityHeaders.dblControlId, dbo.tblTrailerActivityHeaders.lngTrailerUnloadStatus,
dbo.tblTrailerActivityHeaders.lngTrailerUnloadLocation, dbo.tblTrailerActivityHeaders.dteTrailerUnloadDate,
dbo.tblTrailerActivityHeaders.txtTrailerDispatchUserId,
dbo.tblTrailerActivityHeaders.lngTrailerLoadTypeId AS tblTrailerActivityHeaders_lngTrailerLoadTypeId,
dbo.tblTrailerUtilizationDetails.lngTrailerLoadTypeId AS tblTrailerUtilizationDetails_lngTrailerLoadTypeId,
dbo.tblTrailerUtilizationDetails.txtShowNumber AS tblTrailerUtilizationDetails_txtShowNumber,
dbo.tblTrailerActivityHeaders.txtShowNumber AS tblTrailerActivityHeaders_txtShowNumber,
dbo.tblTrailerUtilizationDetails.lngTrailerUtilizationDetailsId
FROM dbo.tblTrailerActivityHeaders LEFT OUTER JOIN
dbo.tblTrailerUtilizationDetails ON dbo.tblTrailerActivityHeaders.lngTrailerActivityHeaderId = dbo.tblTrailerUtilizationDetails.lngTrailerActivityHeaderId
GO
/****** Object: Trigger [dbo].[trg_vw_TrailerActivityHeaders_withUtilizationDetails_InsteadOfInsert] Script Date: 07/06/2010 14:19:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trg_vw_TrailerActivityHeaders_withUtilizationDetails_InsteadOfInsert] ON [dbo].[vw_TrailerActivityHeaders_withUtilizationDetails]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO tblTrailerActivityHeaders
([txtTrailerDOTNumber]
,[dteTrailerLoadDate]
,[lngTrailerLoadLocation]
,[lngTrailerLoadStatus]
,[txtTrailerSealNumber]
,[intUsageTypeId]
,[txtEntryUserId]
,[dteEntryDateTime]
,[dteTrailerUnloadDate]
,[lngTrailerUnloadLocation]
,[lngTrailerUnloadStatus]
,[txtShowNumber]
,[lngTrailerLoadTypeId])
SELECT
[txtTrailerDOTNumber]
,[dteTrailerLoadDate]
,[lngTrailerLoadLocation]
,[lngTrailerLoadStatus]
,[txtTrailerSealNumber]
,[intUsageTypeId]
,[txtEntryUserId]
,[dteEntryDateTime]
,[dteTrailerUnloadDate]
,[lngTrailerUnloadLocation]
,[lngTrailerUnloadStatus]
,[tblTrailerUtilizationDetails_txtShowNumber]
,[tblTrailerUtilizationDetails_lngTrailerLoadTypeId]
FROM inserted
INSERT INTO tblTrailerUtilizationDetails
(lngTrailerActivityHeaderId
,lngTrailerLoadTypeId
,txtShowNumber)
SELECT
SCOPE_IDENTITY() as expr1
,[tblTrailerUtilizationDetails_lngTrailerLoadTypeId]
,[tblTrailerUtilizationDetails_txtShowNumber]
FROM inserted
END
GO
/****** Object: Trigger [dbo].[trg_vw_TrailerActivityHeaders_withUtilizationDetails_InsteadOfUpdate] Script Date: 07/06/2010 14:19:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_vw_TrailerActivityHeaders_withUtilizationDetails_InsteadOfUpdate] ON [dbo].[vw_TrailerActivityHeaders_withUtilizationDetails]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE tblTrailerActivityHeaders SET
[txtTrailerDOTNumber] = inserted.txtTrailerDOTNumber
,[dteTrailerLoadDate] = inserted.dteTrailerLoadDate
,[lngTrailerLoadLocation] = inserted.lngTrailerLoadLocation
,[lngTrailerLoadStatus] = inserted.lngTrailerLoadStatus
,[txtTrailerSealNumber] = inserted.txtTrailerSealNumber
,[intUsageTypeId] = inserted.intUsageTypeId
,[txtEntryUserId] = inserted.txtEntryUserId
,[dteEntryDateTime] = inserted.dteEntryDateTime
,[dteTrailerUnloadDate] = inserted.dteTrailerUnloadDate
,[lngTrailerUnloadLocation] = inserted.lngTrailerUnloadLocation
,[lngTrailerUnloadStatus] = inserted.lngTrailerUnloadStatus
,[lngTrailerLoadTypeId] = inserted.tblTrailerUtilizationDetails_lngTrailerLoadTypeId
FROM tblTrailerActivityHeaders, inserted
WHERE
tblTrailerActivityHeaders.lngTrailerActivityHeaderId = inserted.lngTrailerActivityHeaderId
UPDATE tblTrailerUtilizationDetails SET
lngTrailerLoadTypeId = inserted.tblTrailerUtilizationDetails_lngTrailerLoadTypeId
FROM
tblTrailerUtilizationDetails, inserted
WHERE tblTrailerUtilizationDetails.lngTrailerUtilizationDetailsId = inserted.lngTrailerUtilizationDetailsId
END
GO
INSERT INTO tblTrailerActivityHeaders
(txtTrailerDOTNumber, dteTrailerLoadDate, lngTrailerLoadTypeId)
SELECT
'10075' AS Expr1,
'2010-7-1' as Expr2,
0 as Expr3
INSERT INTO tblTrailerUtilizationDetails
(lngTrailerActivityHeaderId, lngTrailerLoadTypeId)
SELECT
IDENT_CURRENT('tblTrailerActivityHeaders'),
1 as Expr1
INSERT INTO tblTrailerUtilizationDetails
(lngTrailerActivityHeaderId, lngTrailerLoadTypeId)
SELECT
IDENT_CURRENT('tblTrailerActivityHeaders'),
2 as Expr1
July 6, 2010 at 2:11 pm
there is no way around it (for the ones where there are multiple parent rows in the view result set).
I recommend you use stored procedures to retrieve and update your data instead of views. If you can't do that, you'll need to make your instead of triggers more sophisticated than they are now.
The probability of survival is inversely proportional to the angle of arrival.
July 6, 2010 at 2:16 pm
sturner (7/6/2010)
there is no way around it (for the ones where there are multiple parent rows in the view result set).I recommend you use stored procedures to retrieve and update your data instead of views. If you can't do that, you'll need to make your instead of triggers more sophisticated than they are now.
So feed all of the parameters into the sp and let it handle updating the various records?
My experience with SQL is limited hence the reason why the trigger isn't all that. I'm assuming that the trigger can call the stored procedure and pass it the values from the 'inserted' table?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply