More Trigger Help Needed
I have a table with some fields in it. Four of them are:
RecID
StepNum
Product
Group
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]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.myTable SET StepNum = (SELECT [StepNum] FROM INSERTED),
OtherFields = (SELECT OtherFields FROM INSERT)
WHERE (StepNum = DELETED) AND (Group = DELETED)
END
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.
RecID
StepNum
Product
Group
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]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.myTable SET StepNum = (SELECT [StepNum] FROM INSERTED),
OtherFields = (SELECT OtherFields FROM INSERT)
WHERE (StepNum = DELETED) AND (Group = DELETED)
END
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.
Not sure exactly what you are doing here (maybe because it is late on a Sunday night). It appears you are setting StepNum to StepNum and OtherFields to OtherFields in the same table that is firing the trigger.
DELETED contains the values before the update took place and INSERTED contains the new values. If you update the values of StepNum or Group the WHERE clause will always be false. The values in the table (dbo.myTable) will ALWAYS equal the values in INSERTED in an AFTER UPDATE trigger, so you are just updating them to themselves.
However, in case I am missing something, the following should work for multiple updates.
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.myTable SET StepNum = INSERTED.StepNum,
OtherFields = INSERTED.OtherFields
FROM INSERTEDINNER JOIN deleted
ON INSERTED.RecID = DELETED.RecID
END
DELETED contains the values before the update took place and INSERTED contains the new values. If you update the values of StepNum or Group the WHERE clause will always be false. The values in the table (dbo.myTable) will ALWAYS equal the values in INSERTED in an AFTER UPDATE trigger, so you are just updating them to themselves.
However, in case I am missing something, the following should work for multiple updates.
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.myTable SET StepNum = INSERTED.StepNum,
OtherFields = INSERTED.OtherFields
FROM INSERTEDINNER JOIN deleted
ON INSERTED.RecID = DELETED.RecID
END
ASKER
That looks like it would work with when I want to increase a series of StepNums by +1 . But it doesn't look like it will work if I am updating any of the other values.
Change some in query
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE T1 SET StepNum = INSERTED.StepNum+1
FROM dbo.myTable as T1, INSERTED,DELETED
WHERE T1.OtherFields = INSERTED.OtherFields AND (T.StepNum = StepNum ) AND (T1.Group = DELETED.Group)
END
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE T1 SET StepNum = INSERTED.StepNum+1
FROM dbo.myTable as T1, INSERTED,DELETED
WHERE T1.OtherFields = INSERTED.OtherFields AND (T.StepNum = StepNum ) AND (T1.Group = DELETED.Group)
END
It's depend on condition, the trigger work with any field change
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE T1 SET StepNum = INSERTED.StepNum+1
FROM dbo.myTable as T1, INSERTED,DELETED
WHERE T1.OtherFields = INSERTED.OtherFields AND (T1.StepNum = DELETED.StepNum ) AND (T1.Group = DELETED.Group)
END
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE T1 SET StepNum = INSERTED.StepNum+1
FROM dbo.myTable as T1, INSERTED,DELETED
WHERE T1.OtherFields = INSERTED.OtherFields AND (T1.StepNum = DELETED.StepNum ) AND (T1.Group = DELETED.Group)
END
ASKER
dbbishop --
I have these records:
ID StepNum Product Group OtherFields
n 1 Apples 20 7/22/07
n 2 Apples 20 7/22/07
n 3 Apples 20 7/22/07
n 4 Apples 20 7/22/07
n 5 Apples 20 7/22/07
n 1 Pears 20 7/22/07
n 2 Pears 20 7/22/07
n 3 Pears 20 7/22/07
n 4 Pears 20 7/22/07
n 5 Pears 20 7/22/07
The Trigger needs to handle this:
UPDATE myTable SET OtherFields = '8/21/07' WHERE StepNum = 2 AND Group = 20
It also must be able to handle this:
UPDATE myTable SET StepNum = StepNum+1 WHERE (StepNum > 1 AND StepNum < 4) AND Group = 20
I have these records:
ID StepNum Product Group OtherFields
n 1 Apples 20 7/22/07
n 2 Apples 20 7/22/07
n 3 Apples 20 7/22/07
n 4 Apples 20 7/22/07
n 5 Apples 20 7/22/07
n 1 Pears 20 7/22/07
n 2 Pears 20 7/22/07
n 3 Pears 20 7/22/07
n 4 Pears 20 7/22/07
n 5 Pears 20 7/22/07
The Trigger needs to handle this:
UPDATE myTable SET OtherFields = '8/21/07' WHERE StepNum = 2 AND Group = 20
It also must be able to handle this:
UPDATE myTable SET StepNum = StepNum+1 WHERE (StepNum > 1 AND StepNum < 4) AND Group = 20
ASKER
This will allow updating any of Otherfields being propagated throughout myTable for a given step number (in this case 2) for all products within a group.
But I am also trying to accommodate changes to several records for all products within a group simultaneously. Like shuffling the StepNums of steps 2, 3, and 4 of every product within group 20 with a single instruction.
But I am also trying to accommodate changes to several records for all products within a group simultaneously. Like shuffling the StepNums of steps 2, 3, and 4 of every product within group 20 with a single instruction.
It may use this for your purpose!
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE T1 SET StepNum = StepNum+1
FROM dbo.myTable as T1, DELETED
WHERE (T1.StepNum > 1 AND T1.StepNum < 4) AND T1.Group = DELETED.Group
END
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE T1 SET StepNum = StepNum+1
FROM dbo.myTable as T1, DELETED
WHERE (T1.StepNum > 1 AND T1.StepNum < 4) AND T1.Group = DELETED.Group
END
ASKER
Late at night -- not thinking clearly either!!
For Example, I can issue a command to update the date for Step 2 of Apples, and the trigger will also update the date for step 2 of Pears to that same value because it is in the same group.
BUT, I also need to issue a single command to change multiple steps of Apples simultaneously and the trigger must perform the same simultaneous update for the same collection of steps for Pears.
For Example, I can issue a command to update the date for Step 2 of Apples, and the trigger will also update the date for step 2 of Pears to that same value because it is in the same group.
BUT, I also need to issue a single command to change multiple steps of Apples simultaneously and the trigger must perform the same simultaneous update for the same collection of steps for Pears.
ASKER
HuyBD --
You are having me do the StepNum + 1 INSIDE the trigger.
I do not want to do that inside the trigger because sometimes the trigger will only be asked to update a field other than StepNum.
Look at my latest comment ID 19544903
You are having me do the StepNum + 1 INSIDE the trigger.
I do not want to do that inside the trigger because sometimes the trigger will only be asked to update a field other than StepNum.
Look at my latest comment ID 19544903
ASKER
Don't want you to think I am ignoring your rapid responses, but it is way past my bedtime!!! I will study these responses more closely in the morning.
Sorry, I missed the StepNum+1 in the UPDATE statement. That's why I like spaces around all my operators.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you also do this in one trigger, or does it have to be multiple triggers?
if update(fldOne)
begin
update .......
end
if update(fldTwo)
begin
update ......
end
if update(fldOne)
begin
update .......
end
if update(fldTwo)
begin
update ......
end
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATETRIGGER [Propagate Updates Within Groups] ON [dbo].[myTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE T1 SET StepNum = INSERTED.StepNum+1
FROM dbo.myTable as T1, INSERTED
WHERE T1.OtherFields = INSERTED.OtherFields AND (StepNum = DELETED) AND (Group = DELETED)
END
Hope this help!