Solved

ms sql server 2005 error with trigger

Posted on 2011-03-18
4
359 Views
Last Modified: 2012-05-11
here is the section of code that I ge the following errors from...

--


-------------------------------------------------------------------------------------

-- 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)
@old_grp_msg = (SELECT rolling_convo_msg from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number)

-- 2. then tack on the newest response + NEW LINE SPACE!!!

declare @grp_tc_id nvarchar(max)
@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)
@new_part = @old_grp_msg + '%0a' + @new_part

-- 3. get the last 500 chars into a new var
update grp_campaign set rolling_convo_msg = RIGHT(@new_part,500)
where tc_id = @grp_tc_id

-- 4. send update msg to all participats
insert into message_queue (to_number,campaign_message)
SELECT to_number, rolling_convo_msg as 'campaign_message'
where tc_id = @grp_tc_id



END


--

Msg 102, Level 15, State 1, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 85
Incorrect syntax near '@old_grp_msg'.
Msg 102, Level 15, State 1, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 91
Incorrect syntax near '@grp_tc_id'.
Msg 102, Level 15, State 1, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 95
Incorrect syntax near '@new_part'.
Msg 137, Level 15, State 2, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 98
Must declare the scalar variable "@new_part".
Msg 137, Level 15, State 2, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 104
Must declare the scalar variable "@grp_tc_id".
0
Comment
Question by:GlobaLevel
[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
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:EL_Barbado
ID: 35169983
You need to use the SELECT clause in front of the variable when populating variables from SQL Statements. Try the code below instead. Im sure it will work nicely for you.


-- 1. get the old message into a var

declare @old_grp_msg nvarchar(max)
SELECT @old_grp_msg = rolling_convo_msg from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number

-- 2. then tack on the newest response + NEW LINE SPACE!!!

declare @grp_tc_id nvarchar(max)
SELECT @grp_tc_id = TC_ID from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number)

declare @new_part nvarchar(max)
SELECT @new_part = @old_grp_msg + '%0a' + @new_part

-- 3. get the last 500 chars into a new var
update grp_campaign set rolling_convo_msg = RIGHT(@new_part,500)
where tc_id = @grp_tc_id

-- 4. send update msg to all participats
insert into message_queue (to_number,campaign_message)
SELECT to_number, rolling_convo_msg as 'campaign_message'
where tc_id = @grp_tc_id



END
0
 
LVL 2

Accepted Solution

by:
EL_Barbado earned 500 total points
ID: 35169988
I've neglected to remove an unneeded parenthesis in #2.

Try this instead for #2
-- 2. then tack on the newest response + NEW LINE SPACE!!!

declare @grp_tc_id nvarchar(max)
SELECT @grp_tc_id = TC_ID from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number

declare @new_part nvarchar(max)
SELECT @new_part = @old_grp_msg + '%0a' + @new_part
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35169995
I figured it out myself...i needed the set statement..you used the select...either way enjoy the points...
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35169998
I was going to reward points anyhow...though I figured it myself...
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

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.

Join & Ask a Question