This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.
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.
[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
-
4
3
2
9 Comments
Active 2 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 2 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
We just refreshed our COA course based on the Newton exam. With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month6 days, 9 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.