July 19, 2010 at 2:04 pm
I've managed to piece together the following trigger which mostly works. The purpose of the trigger is to update the oldest child record for a parent record.
The logic of the trigger is to assign the child records row numbers using Row_Number, select the oldest by selecting the child record with a value of '1' and then update only that one record leaving the others untouched.
'The Trigger
ALTER TRIGGER [dbo].[trg_TrailerActivityHeaders_OnUpdate] ON [dbo].[tblTrailerActivityHeaders] FOR UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Only update the oldest Trailer Utilization Detail Child Record
UPDATE
tblTrailerUtilizationDetails
SET
lngTrailerLoadTypeId = TAH_1.lngTrailerLoadTypeId, txtShowNumber = TAH_1.txtShowNumber
FROM
(
SELECT
TAH.RN,TAH.lngTrailerActivityHeaderId, TAH.lngTrailerLoadTypeId, TAH.txtShowNumber
FROM
(
SELECT
tblTrailerActivityHeaders.lngTrailerActivityHeaderId, tblTrailerActivityHeaders.lngTrailerLoadTypeId, tblTrailerActivityHeaders.txtShowNumber,
ROW_NUMBER() OVER (PARTITION BY tblTrailerUtilizationDetails.lngTrailerActivityHeaderId ORDER BY tblTrailerUtilizationDetails.lngTrailerUtilizationDetailsId) as RN
FROM
tblTrailerActivityHeaders LEFT JOIN tblTrailerUtilizationDetails ON tblTrailerActivityHeaders.lngTrailerActivityHeaderId = tblTrailerUtilizationDetails.lngTrailerActivityHeaderId
) TAH
WHERE RN = 1
) TAH_1
WHERE
tblTrailerUtilizationDetails.lngTrailerActivityHeaderId = TAH_1.lngTrailerActivityHeaderId
END
GO
'Parent table and child table
USE [Sandbox]
GO
/****** Object: Table [dbo].[tblTrailerActivityHeaders] Script Date: 07/19/2010 15:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTrailerActivityHeaders](
[lngTrailerActivityHeaderId] [int] IDENTITY(1000,1) NOT NULL,
[txtTrailerDOTNumber] [nvarchar](15) NOT NULL,
[txtShowNumber] [nvarchar](8) NULL,
[lngTrailerLoadTypeId] [tinyint] 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]
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] NULL,
[lngClientId] [int] 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
GO
'Add data to the parent, create multiple child records per parent
INSERT INTO
tblTrailerActivityHeaders (txtTrailerDOTNumber, txtShowNumber, lngTrailerLoadTypeId)
SELECT
'TEST1','000000','1'
GO
INSERT INTO
tblTrailerActivityHeaders (txtTrailerDOTNumber, txtShowNumber, lngTrailerLoadTypeId)
SELECT
'TEST2','000000','2'
GO
INSERT INTO
tblTrailerActivityHeaders (txtTrailerDOTNumber, txtShowNumber, lngTrailerLoadTypeId)
SELECT
'TEST3','000000','3'
GO
INSERT INTO
tblTrailerActivityHeaders (txtTrailerDOTNumber, txtShowNumber, lngTrailerLoadTypeId)
SELECT
'TEST4','000000','4'
GO
INSERT INTO
tblTrailerActivityHeaders (txtTrailerDOTNumber, txtShowNumber, lngTrailerLoadTypeId)
SELECT
'TEST5','000000','5'
GO
INSERT INTO
tblTrailerUtilizationDetails (lngTrailerActivityHeaderId, txtShowNumber, lngTrailerLoadTypeId)
SELECT
lngTrailerActivityHeaderId, txtShowNumber, lngTrailerLoadTypeId
FROM tblTrailerActivityHeaders
INSERT INTO
tblTrailerUtilizationDetails (lngTrailerActivityHeaderId, txtShowNumber, lngTrailerLoadTypeId)
SELECT
lngTrailerActivityHeaderId, txtShowNumber, lngTrailerLoadTypeId
FROM tblTrailerActivityHeaders
INSERT INTO
tblTrailerUtilizationDetails (lngTrailerActivityHeaderId, txtShowNumber, lngTrailerLoadTypeId)
SELECT
lngTrailerActivityHeaderId, txtShowNumber, lngTrailerLoadTypeId
FROM tblTrailerActivityHeaders
GO
July 19, 2010 at 7:47 pm
I think I've got it. The join was on the primary key in the parent to the foreign key in the child. The correct join needed to be on the primary key of the child to the primary key of the child in the nested query.
Also, it might have helped if I was pulling records from the inserted record set and not the actual table. I had developed it as a stand alone query to get it up and running without changing the references.
'This looks like its working
CREATE TRIGGER [dbo].[trg_TrailerActivityHeaders_OnUpdate] ON [dbo].[tblTrailerActivityHeaders] FOR UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Only update the oldest Trailer Utilization Detail Child Record
UPDATE
tblTrailerUtilizationDetails
SET
lngTrailerLoadTypeId = TAH_1.lngTrailerLoadTypeId, txtShowNumber = TAH_1.txtShowNumber
FROM
(
SELECT
TAH.RN,TAH.lngTrailerActivityHeaderId, TAH.lngTrailerLoadTypeId, TAH.txtShowNumber, TAH.lngTrailerUtilizationDetailsId
FROM
(
SELECT tblTrailerActivityHeaders.lngTrailerActivityHeaderId, tblTrailerActivityHeaders.lngTrailerLoadTypeId, tblTrailerActivityHeaders.txtShowNumber,tblTrailerUtilizationDetails.lngTrailerUtilizationDetailsId,
ROW_NUMBER() OVER (PARTITION BY tblTrailerUtilizationDetails.lngTrailerActivityHeaderId ORDER BY tblTrailerUtilizationDetails.lngTrailerUtilizationDetailsId) as RN
FROM
tblTrailerActivityHeaders LEFT JOIN tblTrailerUtilizationDetails ON tblTrailerActivityHeaders.lngTrailerActivityHeaderId = tblTrailerUtilizationDetails.lngTrailerActivityHeaderId
) TAH
WHERE RN = 1
) TAH_1
WHERE
tblTrailerUtilizationDetails.lngTrailerUtilizationDetailsId = TAH_1.lngTrailerUtilizationDetailsId
END
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply