• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Trigger Expert Needed (again)

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
0
wsturdev
Asked:
wsturdev
  • 13
  • 13
1 Solution
 
derekkrommCommented:
In your SET @KT_Group_ID statement, you are trying to set a variable that, when running an update on multiple rows, will return more than 1 result. Here's a potential re-write - the syntax may be slightly off as I'm working directly in the browser. Also, the IF UPDATE may not work as you plan. When updating multiple rows, IF UPDATE(column) will evaluate to true if the column is simply part of the update statement. If thats fine, then good. Otherwise, if you're looking to see if the value is *actually* updated, your query is going to get a bit more complicated.


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

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 in (
                  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)
                  AND dbo.Tbl_KT_Instances.Lookup_To_Tbl_KT_Groups_For_KT_Group > 0
                  )
)

END

0
 
derekkrommCommented:
any update?
0
 
wsturdevAuthor Commented:
Sorry - I have been swamped, slowly working through nested crises, and hope to get back to this one sometime this evening.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wsturdevAuthor Commented:
I knew this would happen...  Once I looked at your suggestion and sent my response, I could not stop thinking about it, and don't want to let it go now.

When this trigger takes off, even though the SQL that triggered it might be trying to update 200 records at one time, and Inserted might have 200 rows in it, every one of those will have exactly the same value for Group ID.

So, could I use the same code I already have with one minor modification?

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 = (
---------------------------------------
Instead of this
                  SELECT
---------------------------------------
Use this
                  SELECT TOP 1
---------------------------------------
 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

0
 
derekkrommCommented:
Yes, that should work fine.
0
 
wsturdevAuthor Commented:
OK, I got rid of the error concerning selecting too many records in a sub query.

Then I put in your suggested rewrite.

No more errors, but....
Observe is a checkbox.  If I turn it on for an individual row (step 2) without a group involved, it works.  But when I turn it on for a row (step 2) when a group is involved, it correctly updates all step 2 rows in the same group, BUT ALSO all step 2 rows in all other groups.
0
 
derekkrommCommented:
I'm not sure I completely understand. Could you show some sample data and an update w/ how it should act vs how it does act? That'll help me out a lot.
0
 
wsturdevAuthor Commented:
derekkromm -- I got hopelessly screwed up, so I went back to your suggested rewrite in Post ID 19562304.

Problem is, it does not limit itself to the group.  It updates ALL records.
0
 
wsturdevAuthor Commented:
This is a long explanation, but the issue really is fairly simple.

Tbl_KT_Instances has information about KT Instances and has the following fields:
ID
Other Fields
Lookup_To_Tbl_KT_Groups_For_KT_Group

Every record in Tbl_KT_Instances has some value in Lookup_To_Tbl_KT_Groups_For_KT_Group.  Much of the time, it is 0, indicating the Tbl_KT_Instance record does not belong to a group.  But some have pointers to a valid KT Group.

Tbl_KT_Items talks about steps within a KT Instance and has the following fields:
ID
KT_Step_Number
Observe
Other fields
Lookup_To_Tbl_KT_Instances_For_KT_Instance_ID

Usually, but not always, only one field is updated at a time in a Tbl_KT_Items row.

There may be as many as 100 rows in Tbl_KT_Items for every record in Tbl_KT_Instances.  Each row in Tbl_KT_Items will have a unique KT_Step_Number within those rows associated with a specific Tbl_KT_Instances record.  So the Tbl_KT_Items records with Lookup_To_Tbl_KT_Instances_For_KT_Instance_ID of 1  will have KT_Step_Numbers from 1 to, say, 50.  Then, the Tbl_KT_Items records with Lookup_To_Tbl_KT_Instances_For_KT_Instance of 2 also will have KT_Step_Numbers from 1 to, say, 100

SO ---
If an update of Observe is issued for a record in Tbl_KT_Items that has a value of, say, 4 in KT_Step_Number, and following the chain, that record points to a Tbl_KT_Instances record that has a 0 in Lookup_To_Tbl_KT_Groups_For_KT_Group, the trigger ignores the update and it is applied to that single record.

However,  if an update of Observe is issued for a record in Tbl_KT_Items that has a value of, say, 4 in KT_Step_Number, and following the chain, that record points to a Tbl_KT_Instances record that has a non-zero pointer in Lookup_To_Tbl_KT_Groups_For_KT_Group (meaning that KT Instance is part of a KT Group), the trigger must kick in and also apply the exact same update of Observe to all other Tbl_KT_Items records with a KT_Step_Number of 4 where they also point to their own Tbl_KT_Instances record that also has the same non-zero pointer in Lookup_To_Tbl_KT_Groups_For_KT_Group (in other words the Observe update is applied to all step 4s in Tbl_KT_Items that ultimately belong to the same group).

Whenever I update one or many rows in Tbl_KT_Items that, following the chain, points to a Tbl_KT_Instance record with a 0 value in Lookup_To_Tbl_KT_Groups_For_KT_Group, the trigger correctly does not fire.

The Trigger does kick in and the logic I have works perfectly when I update a single row of Tbl_KT_Items at a time, where, following the chain, there is a non-0 pointer value in Tbl_KT_Instances.Lookup_To_Tbl_KT_Groups_For_KT_Group, as when I issue this SQL command:
UPDATE Tbl_KT_Items SET Observe = 1 WHERE Lookup_To_Tbl_KT_Instances_For_Instance_ID = 563 AND KT_Step_Number = 4
All the step 4s in Tbl_KT_Items in any KT Instance that is part of the KT Group gets updated correctly

The problem comes in when I try to simultaneously update all of the rows in Tbl_KT_Items that are associated with unknown KT Instances that all belong to the same KT Group as defined by their common non-0 value in Tbl_KT_Instances.Lookup_To_Tbl_KT_Groups_For_KT_Group, such as with this command:
UPDATE Tbl_KT_Items SET Observe = 1 WHERE Lookup_To_Tbl_KT_Instances_For_Instance_ID = 563
This is supposed to set Observe to True for every KT_Step_Number in Tbl_KT_Items that is associated with Tbl_KT_Instances record number 563.  But, further, the trigger needs to ascertain that KT Instance 563 has the non-0 value in Lookup_To_Tbl_KT_Groups_For_KT_Gro, and also apply the same updates to all KT Steps of Tbl_KT_Items that is found by following the chain using the "Lookup_To_Tbl_KT_Instances_For_Instance_ID = 563" in the update command.  

So my update command may set 100 steps in Tbl_KT_Items to true, but because they are connected to a KT Instance that is part of a KT Group, then all 100 of those Tbl_KT_Items.KT_Step_Numbers of every one of those other KT Instance in that same KT Group must also be set the same way.



0
 
derekkrommCommented:
Could you still give the data examples so its easier to follow? Thanks!
0
 
derekkrommCommented:
Sorry, cross-posted with you. Reading now...
0
 
derekkrommCommented:
Give this update statement a try:

UPDATE      KTI
SET      [Observe] = I.Observe
FROM      Inserted as I
      INNER JOIN dbo.Tbl_KT_Instances AS KTInst ON I.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTInst.ID
      INNER JOIN dbo.Tbl_KT_Items AS KTI ON I.KT_Step_Number = KTI.KT_Step_Number
      INNER JOIN dbo.Tbl_KT_Instances as KTI_Inst ON KTI.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTI_Inst.ID
WHERE      KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group > 0


Also, be careful about updating the same table/column that the trigger affects. I'm not sure if you'll have a problem, but its possible you go into an endless loop since the same column is constantly being updated.
0
 
wsturdevAuthor Commented:
Still updates every row, regardless of the lookup to group.

Will have to work on this in the morning.
0
 
derekkrommCommented:
whoops, forgot that condition, try this:

UPDATE      KTI
SET      [Observe] = I.Observe
FROM      Inserted as I
      INNER JOIN dbo.Tbl_KT_Instances AS KTInst ON I.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTInst.ID
      INNER JOIN dbo.Tbl_KT_Items AS KTI ON I.KT_Step_Number = KTI.KT_Step_Number
      INNER JOIN dbo.Tbl_KT_Instances as KTI_Inst ON KTI.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTI_Inst.ID
WHERE      KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group > 0
AND KTI_Inst.Lookup_To_Tbl_KT_Groups_For_KT_Group = KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group
0
 
wsturdevAuthor Commented:
This works on single rows if the row is not associated with a group.

It works on single rows if the originating row IS associated with a group (it correctly also updates the same KT_Step_Number associated with all other KT Instances associated with the same group).

But, it does not work when I am updating multiple rows as when I execute this command:

UPDATE Tbl_KT_Items SET Observe = 1 WHERE Lookup_To_Tbl_KT_Instances_For_Instance_ID = 563

Which in effect says turn Observe ON in every row in Tbl_KT_Items where the associated KT Instance is part of a KT Group.
0
 
wsturdevAuthor Commented:
By the way,

If I understand correctly, when I issue this command...

UPDATE Tbl_KT_Items SET Observe = 1 WHERE Lookup_To_Tbl_KT_Instances_For_Instance_ID = 563

...and there are 100 KT Steps in Tbl_KT_Items that point to KT Instance 563, there will be 100 rows in Inserted.  They will all be identical except for the KT_Step_Number.

In order to follow the chain into Tbl_KT_Instances to get the Group ID, it is only necessary to look at the first row of Inserted.
0
 
derekkrommCommented:
"But, it does not work when I am updating multiple rows as when I execute this command"

error, updating too much, or not updating enough?
0
 
wsturdevAuthor Commented:
Sorry!! I thought I got enough sleep last night, but now I am not so sure!!

It returns the error "Subquery returned more than 1 value" and no update occurs at all.
0
 
derekkrommCommented:
Does this query return the correct records to be updated? (assuming instance_id=563)

SELECT KTI.*
FROM  (SELECT * FROM Tbl_KT_Items WHERE Lookup_To_Tbl_KT_Instances_For_Instance_ID = 563) AS I
      INNER JOIN dbo.Tbl_KT_Instances AS KTInst ON I.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTInst.ID
      INNER JOIN dbo.Tbl_KT_Items AS KTI ON I.KT_Step_Number = KTI.KT_Step_Number
      INNER JOIN dbo.Tbl_KT_Instances as KTI_Inst ON KTI.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTI_Inst.ID
WHERE      KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group > 0
AND KTI_Inst.Lookup_To_Tbl_KT_Groups_For_KT_Group = KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group
0
 
wsturdevAuthor Commented:
Yes, it does.  There are 18 KT Instances, each with 82 associated KT_Items records, and the query returns 1476 records, which is the number of records in which I am trying to set Observe to true.
0
 
wsturdevAuthor Commented:
If my understanding of "Inserted" is correct, since there are 82 records in Tbl_KT_Items that all point to Tbl_KT_Instances record 563, then when I issue the mass update command, Inserted should have exact copies of those 82 Tbl_KT_Items records.  Correct?

So, I made a copy of Tbl_KT_Items and stripped out all but the 82 records that point to Tbl_KT_Instances record 563.

Then I modified the latest code you asked me to try use the new table I created to mimic Inserted.  It is called Tbl_XXX.


UPDATE      KTI
SET      [Observe] = I.Observe
FROM      Tbl_XXX as I
      INNER JOIN dbo.Tbl_KT_Instances AS KTInst ON I.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTInst.ID
      INNER JOIN dbo.Tbl_KT_Items AS KTI ON I.KT_Step_Number = KTI.KT_Step_Number
      INNER JOIN dbo.Tbl_KT_Instances as KTI_Inst ON KTI.Lookup_To_Tbl_KT_Instances_For_Instance_ID = KTI_Inst.ID
WHERE      KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group > 0
AND KTI_Inst.Lookup_To_Tbl_KT_Groups_For_KT_Group = KTInst.Lookup_To_Tbl_KT_Groups_For_KT_Group

And it worked!!!  The correct 1476 records got updated.

So what is going wrong in the trigger?

Somewhere in the haze and sleeplessness of the last couple of days, I inadvertently stopped testing the turning on/off of the Observe column and started trying to turn on/off the Perform column.

MY APOLOGIES!!!!!

But, now that brings up a question.

Can I have multiple sections in a single trigger to handle different circumstances by saying "IF UPDATE([Observe])"?  Can one of those sections have "mass-update" logic and one have single row logic?

When I issue an update on a single field, what shows up in Inserted?  All columns of the row being updated even though I am updating only one column?  Or, does Inserted just contain the column I am updating and the other referenced columns, such as Lookup_To_Tbl_KT_Instances_For_Instance_ID?  Or does it just contain only the columns I am updating, in this case "Observe"?
0
 
derekkrommCommented:
>>Can I have multiple sections in a single trigger to handle different circumstances by saying "IF UPDATE([Observe])"?  Can one of those sections have "mass-update" logic and one have single row logic?

yes. you can do "if update(observe) begin ... end  if update(columnA) begin ... end ...etc"

>>When I issue an update on a single field, what shows up in Inserted?  All columns of the row being updated even though I am updating only one column?  Or, does Inserted just contain the column I am updating and the other referenced columns, such as Lookup_To_Tbl_KT_Instances_For_Instance_ID?  Or does it just contain only the columns I am updating, in this case "Observe"?

I believe it contains the entire row (all columns)
0
 
wsturdevAuthor Commented:
>>I believe it contains the entire row (all columns)

But somehow the trigger knows that only Observe is being updated?  That is how "If UPDATE([Observe]) works?
0
 
derekkrommCommented:
Yes. There is an INSERTED and a DELETED

IF UPDATE evalutes to true if INSERTED.field <> DELETED.field and false if INSERTED.field = DELETED.field
0
 
wsturdevAuthor Commented:
Thank you for all your help.  You are a Trigger Genius!!!!!

If I knew your email address, I would send you a virtual beer!!!
You have been very graceous to put up with me!
0
 
derekkrommCommented:
No problem. I thought you would ask this additional question:

What happens if there are multi-row updates and a column is only changed in one row?

To pre-emptively answer that for future reference: if a column is changed in at least one row, it is considered to be updated for the entire query.

Feel free to send me that virtual beer: dkromm AT swirnow DOT com :)

Glad I could help out
0

Featured Post

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.

  • 13
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now