MSDE triggers

I need help finish off a trigger for a table in my database. I want the trigger to update a table on condition that it meets two select queries. Take a look at the trigger below, where am i going wrong

Alter Trigger "ozekisms1_Trigger1"
On dbo.ozekisms1
For Insert
as Declare @tel NVARCHAR(160)
Declare @send VARCHAR(30)
begin
if exists (select sender from ozekisms1
join tblCustomer
on ozekisms1.sender=tblCustomer.mobilenum where tblCustomer.status='continue')
SELECT @tel=Information
FROM tblTopic INNER JOIN
   ozekisms1 ON tblTopic.Information = ozekisms1.msg
else
Select @send=sender FROM ozekisms1
Insert into ozekismsout(receiver,msg) values (@send,@tel)

End
The triggered table recieves a message from a user. First off I want to find out if they are registered with the system before I go any further, this is done by checking the username in triggered table with the table that contains the registered members.
The user sends a message as well and I want to check that this message corresponds with a hotword in another table. If it does I want to extract another column in that table. Following this I want to insert the users registered number and the corresponding column into the message out table.

please help!
bono03Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharperCommented:
If the error is in your else statement it is because only one line is associated with the else statement unless placed in a begin...end block

Alter Trigger "ozekisms1_Trigger1"
On dbo.ozekisms1
For Insert
as
     Declare @tel NVARCHAR(160)
     Declare @send VARCHAR(30)
begin
     if exists (
          select
               sender
          from
               ozekisms1
                    join tblCustomer on
                         ozekisms1.sender=tblCustomer.mobilenum
          where
               tblCustomer.status='continue')
               SELECT
                    @tel=Information
               FROM
                    tblTopic
                         INNER JOIN ozekisms1 ON
                              tblTopic.Information = ozekisms1.msg
     else
     begin
          Select
               @send=sender
          FROM
               ozekisms1
          Insert into ozekismsout(receiver,msg) values (@send,@tel)
     end
End
0
bono03Author Commented:
How can i have both :
 "if exists (
         select sender
         from ozekisms1 join tblCustomer on
         ozekisms1.sender=tblCustomer.mobilenum
         where tblCustomer.status='continue')"
and:
"SELECT @tel=Information
    FROM tblTopic INNER JOIN ozekisms1 ON
    tblTopic.Information = ozekisms1.msg"

as conditions that must be true in order for the ozekismsout table to be updated accordingly?
0
nigelrivettCommented:
Use the inserted table in the trigger otherwise you will be checking the whole table

if exists (select sender from inserted i
join tblCustomer
on i.sender=tblCustomer.mobilenum where tblCustomer.status='continue')
begin
insert ozekismsout(receiver,msg)
SELECT i.sender, t.Information
FROM inserted i INNER JOIN
  tblTopic t ON t.Information = i.msg
end

If you want the insert to happen whether or not it's a hotword
insert ozekismsout(receiver,msg)
SELECT i.sender, t.Information
FROM inserted i left outer join
  tblTopic t ON t.Information = i.msg


you could also make this into a single statement rather than an if followed by an insert but maybe it's easier to understand as it is.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bono03Author Commented:
Thats not working for me either Im afraid. This problem is proving rather elusive to figure out.

Will you take a look at my table structure and perhaps with your help I can fix the problem.

The tables are outlined as follows:
The trigger is on the following table

"ozekisms1"
sender
msg

"tblCustomer" contains all the members
mobilenum
fname
lname
status

"tbltopics" consists of the information that should match the messagein.msg
Topic
Information

"ozekismsout" consists of the table I wish to update using the trigger
receiver
msg

First off messagein.sender should be equal to tblCustomer.mobilenum and tblCustomer.staus should be value "continue". If the sender is not a member or is a member but doesn't have status "continue". I want the trigger to quit and update nothing

If this condition is met then I want to select tbltopics.information where it is equal to messagein.msg. If this is satisfied, the value for information should be entered into messageout.msg and mssagein.sender should be entered into messageout.receiver. If the condition was not met I want the trigger to quit and update nothing

Basically the subscribed user is looking for information back from the table tbltopic based on their keyword. If this keyword doesn't match nothing should happen.


0
nigelrivettCommented:
I assume you want this to run when a row is inserted?
Then you will need the inserted table.

if exists (select * from inserted i
join tblCustomer
on i.sender=tblCustomer.mobilenum where tblCustomer.status='continue')

will do the first check.

insert ozekismsout(receiver,msg)
SELECT i.sender, t.Information
FROM inserted i INNER JOIN
 tblTopic t ON t.Information = i.msg

This will insert the values into ozekismsout only if there is a match on t.Information = i.msg.

could do
insert ozekismsout(receiver,msg)
SELECT i.sender, t.Information
FROM inserted i INNER JOIN
 tblTopic t ON t.Information = i.msg
where exists (select * from inserted i
join tblCustomer
on i.sender=tblCustomer.mobilenum where tblCustomer.status='continue')

or

insert ozekismsout(receiver,msg)
SELECT i.sender, t.Information
FROM inserted i INNER JOIN
 tblTopic t ON t.Information = i.msg
inner join tblCustomer
on i.sender=tblCustomer.mobilenum
where tblCustomer.status='continue'


if this isn't working then I suspect there is something wrong with the data so that it is not getting a match.

I assume there are no errors?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CleanupPingCommented:
bono03:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
arbertCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.  

I will leave the following recommendation for this question in the Cleanup topic area:

Accept Answer From nigelrivett

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Arbert
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.