asked on

ms sql server 2005 error with trigger

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".

EL_Barbado

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

ASKER CERTIFIED SOLUTION

EL_Barbado

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GlobaLevel

ASKER

I figured it out myself...i needed the set statement..you used the select...either way enjoy the points...

GlobaLevel

ASKER

I was going to reward points anyhow...though I figured it myself...