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
MIREESEAsked:
Who is Participating?
 
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
 
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
 
MIREESEAuthor Commented:
Thanks, Pawan

Seems like the solution is updating all records in the detail table instead of the records for this order only.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
>>instead of the records for this order only
How to find records for this order only ?
0
 
MIREESEAuthor 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
 
MIREESEAuthor 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
 
MIREESEAuthor 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
 
MIREESEAuthor Commented:
no data columns of this type are in the trigger code itself.
0
 
MIREESEAuthor 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
 
MIREESEAuthor 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
 
MIREESEAuthor 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
 
MIREESEAuthor Commented:
Thank you so much for your help!
0
 
Scott PletcherSenior DBACommented:
You're welcome!  Glad it worked out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.