I have a table with some fields in it. Four of them are:
StepNum is a sequestial number. For every Product, there will be 5 records, with StepNums 1 through 5. Each Product belongs to a Group.
My Trigger is like this:
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
SET NOCOUNT ON;
UPDATE dbo.myTable SET StepNum = (SELECT [StepNum] FROM INSERTED),
OtherFields = (SELECT OtherFields FROM INSERT)
WHERE (StepNum = DELETED) AND (Group = DELETED)
This works when I change any of OtherFields in a single row and even when I want to change StepNum itself in a single row.
For Example: UPDATE myTable SET StepNum = 20 WHERE StepNum = 5
BUT, how can I have the trigger work properly wen I want to work on multiple rows within a Profuct at the same time, as in: UPDATE myTable SET StepNum = StepNum + 1 WHERE STepNum > 1 AND StepNum < 4.
I get an error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.