Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MSDE triggers

Posted on 2003-02-26
8
Medium Priority
?
398 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
7 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 quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

578 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