Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Solved
MS SQL Server - issue with inserting data into table with trigger on it..
Posted on 2011-03-22
I get this error:
Msg 512, Level 16, State 1, Procedure TRIGGER_GRP_Msg_Queue_Reci
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Msg 512, Level 16, State 1, Procedure TRIGGER_GRP_Msg_Queue_Reci
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
-
4
3
2
9 Comments
Active 5 days ago
Most likely you are setting the value of a field to the result of a query, and that query is returning more than one value. For example:
update tbl
set X = (select Y from Z)
If there is more than one row returned from the sub-query, the update will fail.
update tbl
set X = (select Y from Z)
If there is more than one row returned from the sub-query, the update will fail.
okay here is the code section from trigger...
/*****************
---------
-- REPORT --
--------
******************/
--
-- if the msg is nothing text back to recepient and say...try
again--------------
if @campaign_message = ''
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'Oops, we did not understand ur reply. Probably due
2 spelling error
or u took 2 long to reply. Pls check spelling and resend to' +
@sms_server_number + '. U can reply STOP 2 end.')
END
----------------------------------------------------------------------
---------------
-- if recp...STOP...then STOP...
if @campaign_message = 'STOP'
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'You have been opted out of this campaign. Thank you for
your time.')
Update grp_campaign set stop = 'STOP' where to_number = @to_number and
sms_server_num = @sms_server_number
END
-- if recp...send message..repond appropriatelly...
if @campaign_message <> ''
BEGIN
-- 1. get the old message into a var
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
-- 2. get convo id...
declare @convo_id nvarchar(max)
set @convo_id = (SELECT convo_id from grp_campaign WHERE to_number =
@to_number)
-- 3. then tack on the newest response + NEW LINE SPACE!!!
declare @grp_tc_id nvarchar(max)
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
declare @new_part nvarchar(max)
set @new_part = @campaign_message
--set @new_part = @old_grp_msg + '%0a' + UPPER(@convo_id) + ' :' +
--'%0a' + @new_part + '%0a'
set @new_part = @old_grp_msg + ' <<< ' + UPPER(@convo_id) + ' >>> ' + ' :' +
' ' + @new_part + ' '
-- 4. get the last 500 chars into a new var update grp_campaign set
rolling_convo_msg = RIGHT(@new_part,250) where tc_id = @grp_tc_id
-- 5. send update msg to all participats insert into grp_message_queue
(to_number,rolling_convo_msg,sms_server_num)
SELECT to_number, rolling_convo_msg,sms_server_num from grp_campaign
where tc_id = @grp_tc_id
END
Active 5 days ago
Here is an example of a query that might cause a problem IF it returns more than one row (which is data dependent, so I can't tell by looking at it)
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
You can try adding DISTINCT, which will only fix the problem if the multiple-row issue is due to identical rows:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT DISTINCT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
or you can try adding TOP 1, which will cause the query to work, but may not return the value you expect, if there is more than one possible value:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT TOP 1 rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
This needs to be done for all queries like this in your trigger where a value from a query is being assigned to a variable.
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
You can try adding DISTINCT, which will only fix the problem if the multiple-row issue is due to identical rows:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT DISTINCT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
or you can try adding TOP 1, which will cause the query to work, but may not return the value you expect, if there is more than one possible value:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT TOP 1 rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
This needs to be done for all queries like this in your trigger where a value from a query is being assigned to a variable.
copy/paste...messed it up a bit..
/*****************
---------
-- REPORT --
--------
******************/
--
-- if the msg is nothing text back to recepient and say...try
again--------------
if @campaign_message = ''
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'Oops, we did not understand ur reply. Probably due
2 spelling error
or u took 2 long to reply. Pls check spelling and resend to' +
@sms_server_number + '. U can reply STOP 2 end.')
END
----------------------------------------------------------------------
---------------
-- if recp...STOP...then STOP...
if @campaign_message = 'STOP'
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'You have been opted out of this campaign. Thank you for
your time.')
Update grp_campaign set stop = 'STOP' where to_number = @to_number and
sms_server_num = @sms_server_number
END
-- if recp...send message..repond appropriatelly...
if @campaign_message <> ''
BEGIN
-- 1. get the old message into a var
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
-- 2. get convo id...
declare @convo_id nvarchar(max)
set @convo_id = (SELECT convo_id from grp_campaign WHERE to_number =
@to_number)
-- 3. then tack on the newest response + NEW LINE SPACE!!!
declare @grp_tc_id nvarchar(max)
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
declare @new_part nvarchar(max)
set @new_part = @campaign_message
--set @new_part = @old_grp_msg + '%0a' + UPPER(@convo_id) + ' :' +
--'%0a' + @new_part + '%0a'
set @new_part = @old_grp_msg + ' <<< ' + UPPER(@convo_id) + ' >>> ' + ' :' +
' ' + @new_part + ' '
-- 4. get the last 500 chars into a new var
update grp_campaign set
rolling_convo_msg = RIGHT(@new_part,250) where tc_id = @grp_tc_id
-- 5. send update msg to all participats
insert into grp_message_queue (to_number,rolling_convo_msg,sms_server_num)
SELECT to_number, rolling_convo_msg,sms_server_num from grp_campaign
where tc_id = @grp_tc_id
END
the issue has been explained above, aka queries like this:
we cannot solve/check that, you have to go through the data + code to isolate the issue.
and then solve it by not using such non-set programming code, but SQL (T-SQL) code
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
we cannot solve/check that, you have to go through the data + code to isolate the issue.
and then solve it by not using such non-set programming code, but SQL (T-SQL) code
update c
set rolling_convo_msg = RIGHT(@new_part,250)
from grp_campaign c
where to_number = @to_number
and sms_server_num = @sms_server_number
Featured Post
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written code or looked at an SQL statement before, no problem! ... give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses
Course of the Month6 days, 23 hours left to enroll
589 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.