Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Course Of The Month
8 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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'
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'
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
-
6
-
5
- +2
20 Comments
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
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
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
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
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)
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),@order
,'It works'
End
Return
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)
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),@order
,'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
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
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
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
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!
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
use
DECLARE MyC CURSOR FOR
SELECT distinct emailacc FROM dbo.UserDB2, dbo.OrderStatus WHERE status='ready' and orderperson=userID
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'
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?
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?
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...
sorry to trouble you people again.... but thanks...
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
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
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. 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.
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.
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!
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?
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?
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!
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.
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.
Featured Post
Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 9 hours left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.