Solved
help needed for trigger
Posted on 2004-04-16
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'