<div>
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 
 

<pre><code class="code-10 nolinks" id="code-20-42375571-1">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
</code></pre>
</div>

<div>
Thanks, Pawan 
 
Seems like the solution is updating all records in the detail table instead of the records for this order only.
</div>

<div>
&gt;&gt;instead of the records for this order only 
How to find records for this order only ?
</div>

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

<div>
Please try - 
 
Updated -- 
 

<pre><code class="code-10 nolinks" id="code-20-42375715-1">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
</code></pre>
</div>

<div>
Thanks and Sorry for the late reply. 
 
New records do not seem to be populating now. &nbsp; 
 
I used the above solution, however, I received an &quot;ntext&quot; error using &quot;&nbsp;Update &quot;S&quot; so changed the after insert to &quot;instead of Insert&quot; &nbsp; 
 
 
USE [TR_DB] 
GO 
/****** Object: &nbsp;Trigger [Citations].[trg_LogDetail_NewRowNo] &nbsp; &nbsp;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 ( 
&nbsp; &nbsp; SELECT *, ROW_NUMBER() OVER(PARTITION BY ParentLogNo ORDER BY LogDetailID) AS row_num 
&nbsp; &nbsp; FROM Inserted 
) AS I ON I.ParentLogNo = S.ParentLogNo AND I.LogDetailID = S.LogDetailID 
OUTER APPLY ( 
&nbsp; &nbsp; SELECT MAX(RowNo) AS RowNo_Max 
&nbsp; &nbsp; FROM Citations.LogDetails S2 
&nbsp; &nbsp; WHERE S2.ParentLogNo = i.ParentLogNo 
) AS S_Max
</div>

<div>
The trigger has to be &quot;AFTER&quot; insert, not &quot;INSTEAD OF&quot;. 
 
The trigger syntax looks fine to me. &nbsp;I'd have to see the specific code and error message you got to determine why the AFTER trigger failed.
</div>

<div>
Thanks Scott. Here is the error when I attempt to compile the trigger. Line 7 is &quot;UPDATE S&quot; 
 
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.
</div>

<div>
OK. &nbsp;So look thru the columns used in the trigger and remove any references to text / ntext / image columns. &nbsp;Nothing in the code you gave us indicated a column of that type.
</div>

<div>
no data columns of this type are in the trigger code itself.
</div>

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

<div>
In this table Citations.LogDetails 
Do you have any column of type - &nbsp;text, ntext, or image columns ??
</div>

<div>
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.
</div>

<div>
Not sure exactly what you mean by &quot;based on all the details&quot;, but you'd have to leave out any &quot;details&quot; that were one of the older, obsolete data types (text/ntext/image).
</div>

<div>
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: &nbsp;Trigger [Citations].[trg_LogDetail_NewRowNo] &nbsp; &nbsp;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 &gt;&nbsp;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 ( 
&nbsp; &nbsp;SELECT *, ROW_NUMBER() OVER(PARTITION BY ParentLogNo ORDER BY LogDetailID) AS row_num 
&nbsp; &nbsp; FROM Inserted 
) AS I ON I.LogDetailID = S.LogDetailID AND I.ParentLogNo = S.ParentLogNo 
OUTER APPLY ( 
&nbsp; &nbsp; SELECT MAX(RowNo) AS RowNo_Max 
&nbsp; &nbsp; FROM Citations.LogDetails S2 
&nbsp; &nbsp; WHERE S2.ParentLogNo = i.ParentLogNo 
) AS S_Max
</div>

<div>
Thank you so much for your help!
</div>

<div>
You're welcome! &nbsp;Glad it worked out.
</div>

<div>
<div class="content wysiwyg-content">
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. &nbsp;All RowNo for the related details are updating when the trigger fires. &nbsp; 
 
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 &nbsp; 
&nbsp; &nbsp; SET NOCOUNT ON; 
&nbsp; &nbsp; UPDATE Citations.LogDetails 
&nbsp; &nbsp; SET RowNO = (SELECT MAX(rowno) FROM Citations.LogDetails WHERE ParentLogNo = i.ParentLogNo) +1 
&nbsp; &nbsp; &nbsp; FROM Citations.LogDetails S INNER JOIN Inserted I 
&nbsp; &nbsp; &nbsp; ON S.ParentLogNo = I.ParentLogNo 
&nbsp; &nbsp; &nbsp; WHERE I.ParentLogNo = I.ParentLogNo 
END 
GO
</div>
</div>

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

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

trigger

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Golang, also called Go, is an open source programming language that is a statically-typed language with syntax loosely derived from C, adding automatic memory management, type safety, some dynamic-typing capabilities, additional built-in types such as variable-length arrays and key-value maps, and a large standard library. Go is a general-purpose systems programming language that aims to be efficient both for development and execution with a focus on fast compilation and increased maintainability of large projects. Go was originally targeted at systems programming tasks such as building server/web applications, high throughput middleware and databases.