MSDE triggers

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

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?

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.

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.

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.

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