[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

More Trigger Help Needed

Posted on 2007-07-22
15
Medium Priority
?
224 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:wsturdev
  • 7
  • 5
  • 3
15 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 19544804
It should be use inner join to do

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!
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19544816
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
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19544822
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.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 17

Expert Comment

by:HuyBD
ID: 19544824
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
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 19544842
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
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19544853
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
 
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19544887
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.
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 19544899
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
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19544903
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.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19544925
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
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19544935
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.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19544948
Sorry, I missed the StepNum+1 in the UPDATE statement. That's why I like spaces around all my operators.
0
 
LVL 17

Accepted Solution

by:
HuyBD earned 1000 total points
ID: 19545006
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22713252.html#19544925

In trigger, you can specify changed field to trig

if not update(StepNum)
begin
    update .......
end
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19561204
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
0
 
LVL 15

Assisted Solution

by:dbbishop
dbbishop earned 1000 total points
ID: 19562833
Any field in an update statement will show updated, even if it is set to itself. If you have a 'generic' stored procedure that you use for updating data, where you pass ALL the fields back to the database from your client and update all of them then UPDATE(fieldname) will be true.

If you want to execute the code only if an update actually did occur and the value was changed, then you need to compare the values between the inserted and deleted tables.

There may be an easier way to do it, but I had a pretty complex trigger where I needed to send an email if certain fields actually chaged value. I had something similar to the following:

DECLARE @OriginalField1 CHAR(10)
DECLARE @NewField1 CHAR(10)
DECLARE @OriginalField2 CHAR(10)
DECLARE @NewField2 CHAR(10)

SELECT @OldField1 = Field1,
             @OldField2 = Field2
FROM DELETED

SELECT @NewField1 = Field1,
             @NewField2 = Field2
FROM INSERTED

IF (@OldField1 <> @NewFIeld1)
    BEGIN
         ...
    END

IF (@OldField2 <> @NewField2)
    BEGIN
        ...
    END
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question