?
Solved

MSDE triggers

Posted on 2003-02-26
8
Medium Priority
?
396 Views
Last Modified: 2006-11-17
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!
0
Comment
Question by:bono03
[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
8 Comments
 
LVL 2

Expert Comment

by:Sharper
ID: 8026920
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
 

Author Comment

by:bono03
ID: 8027164
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8027311
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:bono03
ID: 8027570
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
 
LVL 18

Accepted Solution

by:
nigelrivett earned 200 total points
ID: 8027702
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
 

Expert Comment

by:CleanupPing
ID: 9276313
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
 
LVL 34

Expert Comment

by:arbert
ID: 10910688
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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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