troubleshooting Question

Trigger Expert Needed (again)

Avatar of wsturdev
wsturdevFlag for United States of America asked on
SQL
26 Comments1 Solution237 ViewsLast Modified:
Sorry, but this is another in a continuing chain of questions about triggers.  I think I have it solved and then discover another glitch!!

Below is my trigger logic.  It correctly handles propagating updates of a single row throughout a group of records with a common KT_Group_ID.

But it does not work when I update multiple rows, such as with this statement:
UPDATE Tbl_KT_Items SET Participate = 1 WHERE Lookup_To_Tbl_KT_Instances_For_Instance_ID = 123456

It returns the error "Subquery returned more than 1 value".  What am I missing?

CREATE TRIGGER [Propagate Updates Within KT Groups] ON [dbo].[Tbl_KT_Items]
AFTER UPDATE
AS
DECLARE @KT_Group_ID as int
IF UPDATE([Observe])
      --      We are updating Observe, and it could be multiple rows simultaneously
      BEGIN
            SET @KT_Group_ID = (
                  SELECT dbo.Tbl_KT_Instances.Lookup_To_Tbl_KT_Groups_For_KT_Group
                  FROM dbo.Tbl_KT_Items LEFT OUTER JOIN
                         dbo.Tbl_KT_Instances ON dbo.Tbl_KT_Items.Lookup_To_Tbl_KT_Instances_For_Instance_ID = dbo.Tbl_KT_Instances.ID
                  WHERE dbo.Tbl_KT_Items.Lookup_To_Tbl_KT_Instances_For_Instance_ID = (SELECT Lookup_To_Tbl_KT_Instances_For_Instance_ID FROM Inserted)
                  AND dbo.Tbl_KT_Items.ID = (SELECT ID FROM Inserted)
                  )
                  IF @KT_Group_ID > 0
                     UPDATE dbo.Tbl_KT_Items SET
                                [Observe] = I.Observe
                        FROM dbo.Tbl_KT_Items AS KTI
                              INNER JOIN (SELECT KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group
                                                            ,I.*
                                                            FROM Inserted AS I
                                                            INNER JOIN dbo.Tbl_KT_Instances AS KTInst
                                                                  ON I.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTInst.ID
                                                                  AND KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group >0
                                                ) AS I
                                ON KTI.KT_Step_Number = I.KT_Step_Number
                        AND (I.Lookup_To_Tbl_KT_Groups_For_KT_Group = @KT_Group_ID)

      END
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 26 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros