help needed for trigger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Lowfatspread

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

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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!

Problem is your querry...

use

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

Since there was multiple order (5) ready for a user it returns 5 records & 5 emails too.

ASKER CERTIFIED SOLUTION

BulZeyE

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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?

ASKER

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...

ASKER

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...

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

ASKER

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!

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.

ASKER

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!

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?

ASKER

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!

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.