Solved

ms sql server 2005 error with trigger

Posted on 2011-03-18
4
358 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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