Need help with Insert Trigger on inserted detail record using Max() - Thanks

Hello,


I'm trying to increase the rowno column in the detail file for each detail inserted.

Increase the RowNo column in the detail table (on insert) based on the ParentID and the MAX row for the related detail records using a trigger, however, this does not appear to be working.  All RowNo for the related details are updating when the trigger fires.  

Trigger

USE [TR_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Citations].[trg_LogDetail_NewRowNo]
ON Citations.LogDetails
AFTER INSERT
AS
BEGIN  
    SET NOCOUNT ON;
    UPDATE Citations.LogDetails
    SET RowNO = (SELECT MAX(rowno) FROM Citations.LogDetails WHERE ParentLogNo = i.ParentLogNo) +1
      FROM Citations.LogDetails S INNER JOIN Inserted I
      ON S.ParentLogNo = I.ParentLogNo
      WHERE I.ParentLogNo = I.ParentLogNo
END
GO
MichaelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this -

Explanation - You are using the twice Citations.LogDetails , Twice it is not required. We can use cross apply and get the max rno + 1 for each Citations.LogDetails

USE [TR_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Citations].[trg_LogDetail_NewRowNo]
ON Citations.LogDetails
AFTER INSERT
AS 
BEGIN   
	SET NOCOUNT ON;
	UPDATE c
	SET c.RowNO = r.rno
	FROM Citations.LogDetails c
	CROSS APPLY
	(
		SELECT MAX(rowno) + 1 rno 
		FROM Inserted I 
		WHERE c.ParentLogNo = I.ParentLogNo 
	)r
END
GO

Open in new window

0
MichaelAuthor Commented:
Thanks, Pawan

Seems like the solution is updating all records in the detail table instead of the records for this order only.
0
Pawan KumarDatabase ExpertCommented:
>>instead of the records for this order only
How to find records for this order only ?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MichaelAuthor Commented:
Yes, I need to only update the Rowno in the current record based on the max(rowno) for all details for the order.
0
Pawan KumarDatabase ExpertCommented:
Please try -

Updated --

USE [TR_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Citations].[trg_LogDetail_NewRowNo]
ON Citations.LogDetails
AFTER INSERT
AS 
BEGIN   
	SET NOCOUNT ON;
	UPDATE c
	SET c.RowNo = r.rno
	FROM Citations.LogDetails c
	CROSS APPLY
	(
		SELECT rt.ParentLogNo , MAX(rt.rowno) rn
		FROM Citations.LogDetails rt
		WHERE rt.ParentLogNo = c.ParentLogNo
	)w
	CROSS APPLY
	(
		SELECT MAX(rowno) + 1 rno 
		FROM Inserted I 
		WHERE c.ParentLogNo = I.ParentLogNo 
		AND w.ParentLogNo = c.ParentLogNo
		AND I.rowno = w.rn
	)r
END
GO

Open in new window

0
Scott PletcherSenior DBACommented:
You need a unique key for the LogDetails row.  Joining on ParentLogNo is not enough, as there will be multiples of those in the table.

Is there an identity value or other column or set of columns in LogDetails that's known to be unique?  For example, as below.

Note that the code must allow for multiple rows being inserted at the same time, because SQL Server only fires a trigger once per statement, no matter how many rows are affected.  That is, SQL does not have "for each row" triggers like some other dbms's do.

ALTER TRIGGER [Citations].[trg_LogDetail_NewRowNo]
ON Citations.LogDetails
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE S
SET RowNO = S_Max.RowNo_Max + I.row_num
FROM Citations.LogDetails S
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ParentLogNo ORDER BY $IDENTITY) AS row_num
    FROM Inserted
) AS I ON I.ParentLogNo = S.ParentLogNo AND I.$IDENTITY = S.$IDENTITY
OUTER APPLY (
    SELECT MAX(RowNo) AS RowNo_Max
    FROM Citations.LogDetails S2
    WHERE S2.ParentLogNo = i.ParentLogNo
) AS S_Max
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MichaelAuthor Commented:
Thanks and Sorry for the late reply.

New records do not seem to be populating now.  

I used the above solution, however, I received an "ntext" error using " Update "S" so changed the after insert to "instead of Insert"  


USE [TR_DB]
GO
/****** Object:  Trigger [Citations].[trg_LogDetail_NewRowNo]    Script Date: 11/23/2017 1:59:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Citations].[trg_LogDetail_NewRowNo]
ON [Citations].[LogDetails]
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
UPDATE S
SET RowNO = S_Max.RowNo_Max + I.row_num
FROM Citations.LogDetails S
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ParentLogNo ORDER BY LogDetailID) AS row_num
    FROM Inserted
) AS I ON I.ParentLogNo = S.ParentLogNo AND I.LogDetailID = S.LogDetailID
OUTER APPLY (
    SELECT MAX(RowNo) AS RowNo_Max
    FROM Citations.LogDetails S2
    WHERE S2.ParentLogNo = i.ParentLogNo
) AS S_Max
0
Scott PletcherSenior DBACommented:
The trigger has to be "AFTER" insert, not "INSTEAD OF".

The trigger syntax looks fine to me.  I'd have to see the specific code and error message you got to determine why the AFTER trigger failed.
0
MichaelAuthor Commented:
Thanks Scott. Here is the error when I attempt to compile the trigger. Line 7 is "UPDATE S"

Msg 311, Level 16, State 1, Procedure trg_LogDetail_NewRowNo, Line 7 [Batch Start Line 7]
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
0
Scott PletcherSenior DBACommented:
OK.  So look thru the columns used in the trigger and remove any references to text / ntext / image columns.  Nothing in the code you gave us indicated a column of that type.
0
MichaelAuthor Commented:
no data columns of this type are in the trigger code itself.
0
MichaelAuthor Commented:
seems like the trigger is throwing errors based on columns in the table although they are not in the trigger code?
0
Pawan KumarDatabase ExpertCommented:
In this table Citations.LogDetails
Do you have any column of type -  text, ntext, or image columns ??
0
MichaelAuthor Commented:
Yes ,it does contain those column types.

This is a detail table I would like to increase the rownno based on all the details associated the transaction ID.
0
Scott PletcherSenior DBACommented:
Not sure exactly what you mean by "based on all the details", but you'd have to leave out any "details" that were one of the older, obsolete data types (text/ntext/image).
0
MichaelAuthor Commented:
Hi Scott,

I removed the old column types and added code to deal with the first row and everything seems to be PERFECT:). Thanks so much for your help!!

Michael

USE [TR_DB]
GO
/****** Object:  Trigger [Citations].[trg_LogDetail_NewRowNo]    Script Date: 12/2/2017 11:14:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Citations].[trg_LogDetail_NewRowNo]
ON [Citations].[LogDetails]
AFTER INSERT
AS

SET NOCOUNT ON;
UPDATE S

SET RowNO = CASE WHEN S_Max.RowNo_Max > 0 THEN (S_Max.RowNo_Max + I.row_num) ELSE 1 END
--SET RowNO = S_Max.RowNo_Max + I.row_num
FROM Citations.LogDetails S
INNER JOIN (
   SELECT *, ROW_NUMBER() OVER(PARTITION BY ParentLogNo ORDER BY LogDetailID) AS row_num
    FROM Inserted
) AS I ON I.LogDetailID = S.LogDetailID AND I.ParentLogNo = S.ParentLogNo
OUTER APPLY (
    SELECT MAX(RowNo) AS RowNo_Max
    FROM Citations.LogDetails S2
    WHERE S2.ParentLogNo = i.ParentLogNo
) AS S_Max
0
MichaelAuthor Commented:
Thank you so much for your help!
0
Scott PletcherSenior DBACommented:
You're welcome!  Glad it worked out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
trigger

From novice to tech pro — start learning today.