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

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

Select all Open in new window

Thanks, Pawan

Seems like the solution is updating all records in the detail table instead of the records for this order only.

>>instead of the records for this order only
How to find records for this order only ?

Yes, I need to only update the Rowno in the current record based on the max(rowno) for all details for the order.

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

Select all Open in new window

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

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.

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.

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.

no data columns of this type are in the trigger code itself.

seems like the trigger is throwing errors based on columns in the table although they are not in the trigger code?

In this table Citations.LogDetails
Do you have any column of type -  text, ntext, or image columns ??

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.

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).

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

Thank you so much for your help!

You're welcome!  Glad it worked out.