GlobaLevel
asked on
MS SQL Server - issue with inserting data into table with trigger on it..
I get this error:
Msg 512, Level 16, State 1, Procedure TRIGGER_GRP_Msg_Queue_Reci eve, Line 98
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.
We will need to see the query to be of any help.
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.
check the trigger code, it seems it does not handle inserts with multiple rows properly
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
copy/paste...messed it up a bit..
ASKER
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
ASKER
...any help on this..?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.