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.
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
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
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
Thanks, Pawan
Seems like the solution is updating all records in the detail table instead of the records for this order only.
Seems like the solution is updating all records in the detail table instead of the 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 --
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
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
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
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
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
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
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[trg_LogDetail
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
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[trg_LogDetail
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.
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.
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.
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 ??
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.
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[trg_LogDetail
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
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[trg_LogDetail
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
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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
Open in new window