Solved

help needed for trigger

Posted on 2004-04-16
20
536 Views
Last Modified: 2012-06-27
i have 2 tables, one is OrderStatus and UserDB. whenever the status in OrderStatus is updated from 'no' to 'ready', i want it to run the trigger to send email to pple where the items inside the database are updated.

but whenever there are more than one records, the trigger wont work and display an error of
"subquery returns more than 1 value. this is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. i have only tried out the @To part...

pls help! thanks in advance!

trigger:

CREATE TRIGGER hello ON [dbo].[OrderStatus]
FOR UPDATE
AS

DECLARE @From varchar(100)
DECLARE @To varchar (100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar (100)
DECLARE @CC varchar(100)
DECLARE @BCC varchar(100)

SELECT @To = (SELECT emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID)

Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID


exec sp_send_cdontsmail 'myemailaddr',@To,'Test of CDONTS','It works'
0
Comment
Question by:radonfileproject
  • 7
  • 6
  • 5
  • +2
20 Comments
 
LVL 10

Assisted Solution

by:Shailesh15
Shailesh15 earned 35 total points
ID: 10844635
Try This...


CREATE TRIGGER hello ON [dbo].[OrderStatus]
FOR UPDATE
AS

DECLARE @From varchar(100)
DECLARE @To varchar (100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar (100)
DECLARE @CC varchar(100)
DECLARE @BCC varchar(100)
DECLARE @Email varchar(100)

DECLARE MyC CURSOR FOR
SELECT emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID

OPEN MyC
FETCH NEXT FROM MyC INTO @To

WHILE (@@fetch_status = 0)
BEGIN


Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

FETCH NEXT FROM MyC INTO @To
end

CLOSE MyC
DEALLOCATE MyC

RETURN
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10844648
May be You need to move declare's out of loop!..

Replace that...

CREATE TRIGGER hello ON [dbo].[OrderStatus]
FOR UPDATE
AS

DECLARE @From varchar(100)
DECLARE @To varchar (100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar (100)
DECLARE @CC varchar(100)
DECLARE @BCC varchar(100)
DECLARE @Email varchar(100)
Declare @MailID int
Declare @hr int

DECLARE MyC CURSOR FOR
SELECT emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID

OPEN MyC
FETCH NEXT FROM MyC INTO @To


WHILE (@@fetch_status = 0)
BEGIN

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

FETCH NEXT FROM MyC INTO @To
end

CLOSE MyC
DEALLOCATE MyC
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10844815
more like this...........

CREATE TRIGGER hello ON [dbo].[OrderStatus]
FOR UPDATE
AS

DECLARE @From varchar(100)
DECLARE @To varchar (100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar (100)
DECLARE @CC varchar(100)
DECLARE @BCC varchar(100)
declare @maxmail

-- you need to select using the Inserted and deleted tables
-- to identify those orders whose status has changed to ready...
 
Select emailacc,
      ,OrderId,Identity(int,1,1) as rowno
 into #temp
 FROM Inserted as I
 Inner Join Deleted as D
    on I.orderId=D.OrderId
 Inner Join dbo.UserDB2 as U
    on I.OrderPerson=U.Userid
 WHERE I.status='ready' and D.Statis='NO'

set @maxMail=@@Rowcount

Declare @MailID int
Declare @hr int

Declare @rowno int
Declare @OrderID int

Set @rowno=1
While @rowno <=@maxmail
begin
Select @to = emailacc
      ,@orderID=OrderID  
      ,@rowno=@rowno+1
  from #temp
 where Rowno=@rowno
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
exec sp_send_cdontsmail 'myemailaddr',@To
     ,'Test of CDONTS for Order#' + convert(varchar(10),@orderID)
     ,'It works'
End

Return
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10844855
Ooops.. I missed a line from your code while copying...

Add..
****************************
--line missed
exec sp_send_cdontsmail 'myemailaddr',@To,'Test of CDONTS','It works'

FETCH NEXT FROM MyC INTO @To
end

CLOSE MyC
DEALLOCATE MyC
0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 70 total points
ID: 10845165
Sailesh' code should work (this can also be done in a loop without a cursor), but I'll try to explain the reason for your error so you'll know what to do if it comes up again another time. It comes from this line (in your original code)
SELECT @To = (SELECT emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID)

You can only assign one value to a variable (@to) and your select statement in this case is returning more than one row.  

If there is only one emailacc for each userid, you can fix this easily (and use the rest of your original code) by doing this

SELECT distinct @To = emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID

Try running this:
SELECT distinct emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID
if it returns only 1 row, you're fine to use the rest of your code without getting into the cursor provided by Sailesh.

Chris
0
 

Author Comment

by:radonfileproject
ID: 10845411
hi, thanks for replying back!!!!

to Shailesh15: yours work with no error about the subquery returns blah blah, but when i sent the email, i receive the email repeatedly... i mean, i only update one record to 'ready', but i got 5++ emails generated...

to Lowfatspread: i encountered some errors while using your method. but thanks too!

to ChrisFretwell: i tried my previous method with your select distinct thing, but no emails are generated...

i really hope to get this done as soon as possible... thanks!
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10845550
Problem is your querry...

use

DECLARE MyC CURSOR FOR
SELECT distinct emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10845559
Since there was multiple order (5) ready for a user it returns 5 records & 5 emails too.
0
 
LVL 4

Accepted Solution

by:
BulZeyE earned 20 total points
ID: 10845792

Maybe you should just semi-colon delimit the To field in your original like:

CREATE TRIGGER hello ON [dbo].[OrderStatus]
FOR UPDATE
AS

DECLARE @From varchar(100)
DECLARE @To varchar (500)
DECLARE @Subject varchar(100)
DECLARE @Body varchar (100)
DECLARE @CC varchar(100)
DECLARE @BCC varchar(100)

SELECT @To = COALESCE(@To + ';','') +  emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID

Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID


exec sp_send_cdontsmail 'myemailaddr',@To,'Test of CDONTS','It works'
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10845837
What do you get back from

SELECT  emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID

any records?

you return the @hr from eachsp call. Do you check to make sure the @hr value for each is successful?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:radonfileproject
ID: 10846026
seems like the distinct is not working, cos the when i display the orderid in the email, it looks as if the status='ready' and the distinct is not working...

sorry to trouble you people again.... but thanks...
0
 

Author Comment

by:radonfileproject
ID: 10846076
i got the records, but i get alot repeated ones. for example, only record 1 and record 2 is "ready", but i also receive 3, 4 and 5... weird...
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10846081
Take the line put in by BulZeye

SELECT @To = COALESCE(@To + ';','') +  emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID


and change it to this (almost the same, but will only return each user once)

SELECT @To = COALESCE(@To + ';','') +  emailacc FROM dbo.UserDB2 where userid in (select distinct orderperson  from dbo.OrderStatus WHERE status='ready' )

The subquery will select a distinct list of orderperson values and the main query will select email addresses for any userid found in the subquery

Chris
0
 

Author Comment

by:radonfileproject
ID: 10846167
1. erm, now it is like, if i update 5 records, all 5 records will send email, regardless it the status... if i change let say "collected" to "no", the record will also send an email...

2. the problem of repeating emails is solved too! but i do i add more field names into the select statement let say i wan to get the name of the user's too?

thanks!
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10846225
2 - (easier answer) No problem to select whatever you want from the user table. Just declare the variables you want them put into and use the same coalesce select format on those that Sailish originaly indicated
ie

SELECT @To = COALESCE(@To + ';','') +  emailacc, @Name = coalesce(@name + ':','')  FROM dbo.UserDB2 where userid in (select distinct orderperson  from dbo.OrderStatus WHERE status='ready' )

1 - this is harder to get around not knowing when and how etc you are doing this. If this is in your trigger for the order status table then it will send an email for every change you make - thats what a trigger does. If you only want one email, then you can either keep a status somewhere to say you've already sent an email about this one or have the trigger call an sproc or something after all orders have been change. By putting this in a trigger its working exactly like the trigger should, for every update, do this.
0
 

Author Comment

by:radonfileproject
ID: 10846306
thanks for point number 1, but for point number 2, for this statement:  

from dbo.OrderStatus WHERE status='ready'

isn't it already choosing what records to update? and i think that statement did not work at all for my case... can't the update be selective on wat things to update?

my main purpose is to auto generate an email when the orderstatus table when the status is updated to 'ready' only... and this trigger is for my orderstatus table.

thank you very much!
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10846322
if you want this part of the trigger to fire when the status was changed (not anything else) then you can use the inserted table, not the orderstatus table.

So in the subquery, change orderstatus to inserted

SELECT @To = COALESCE(@To + ';','') +  emailacc, @Name = coalesce(@name + ':','')  FROM dbo.UserDB2 where userid in (select distinct orderperson  from inserted WHERE status='ready' )


By going against the orderstatus table, you'll get every record that has a value of ready. by going against the inserted table (yes, its still the inserted table on an update) you'll only get records affected by this update.

Make sense?
0
 

Author Comment

by:radonfileproject
ID: 10846418
hey, thanks really, point 2 works now... but i still got a query on point 1 (Sorry!)

SELECT COALESCE(@To + ';','') +  emailacc, coalesce(@Subject + ':','')  + orderid  FROM dbo.UserDB2, dbo.OrderStatus where userid in (select distinct orderperson  from inserted WHERE status='ready' )

you see, my orderid is from dbo.OrderStatus, if i write it like this, it will result in those wrong things again... so where should i place it?

thanks again!
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10846512
You want that from the inserted table and to get that, you need to change your query to a join instead of a subquery for that


SELECT COALESCE(@To + ';','') +  emailacc, coalesce(@Subject + ':','')  + orderid  FROM dbo.UserDB2 join inserted on userid = orderperson where status = 'ready'

if that returns duplicates then try this

SELECT COALESCE(@To + ';','') +  emailacc, coalesce(@Subject + ':','')  + orderid  FROM
(select distinct emailacc, orderid from dbo.UserDB2 join inserted on userid = orderperson where status = 'ready' ) as A

Along with probably working now, do you understand the nature of the inserted table? The inserted table is where your changes are stored. Its structure will match the table you have the trigger on. so in this case inserted refers to changed values in your orderstatus table.

More than that, in your trigger you can have it only even start into this part of the code by saying you only want it to fire if the orderstatus is one of the fields being changed. Do this by using

IF UPDATE ( status )  

You should refer to books online for more information on using a column specific trigger.
0
 

Author Comment

by:radonfileproject
ID: 10846567
hey thanks! it totally works now! im very grateful for all of you pple's help!!! thanks...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now