send sms when inserting a new data

[besample]

Respected sir,
i'm working in a healthcare Unit. i'm using SQL 2000 server with Visual Basic as frontend. A sms application that send SMS to Patient Mobile, when he got admission.
The Admission module Contains Patient name and Mobile number... When i use the following trigger its not pickup the new record, but take the previous record and sent SMS to that Patient. How can i assign the New record at the top.?

Please refer the querry.

"ALTER TRIGGER TR_IP_PATIENT_S MS ON INPATIENT
FOR INSERT
AS
INSERT INTO SMSDATA (Mobileno,SMSMs g,SMSKey,SMSDat e,SMSstatus,Loc ate)
select top 1
a.pphone ,'PatientName:' + space(1) +a.title+space( 1)+a.firstname+ space(1)+a.midd lename+space(1) +a.lastname+'.' +
'UHID No:'+convert(va rchar(20),a.reg istrationno)+'. '+
'S/A:'+b.name +'/'+ a.age+'.'+
'Ward:'+d.name+ '.'+
'RNo:'+c.name+' .'+
'Doctor Name:'+f.name as SMSMsg,'IT' as smskey,getdate( ) as smsdate , 0 as status ,'IP' as location
from inpatient as a
inner join sex as b on b.id=a.sex
inner join bed as c on c.ipid=a.ipid
inner join station as d on d.id=c .stationid
inner join ipconsultant as e on e.ipid=a.ipid
inner join doctor as f on f.id=e.doctorid
where f.deleted=0
and a.pphone is not null and a.pphone <> ''
order by a.ipid desc
return"

[Delerna]

try this
[code=sql]
INSERT INTO SMSDATA (Mobileno,SMSMs g,SMSKey,SMSDat e,SMSstatus,Loc ate)
select top 1 *
from
(select top 100 percent a.pphone ,'PatientName:' + space(1) +a.title+space( 1)+a.firstname+ space(1)+a.midd lenam e+space(1)+a.la stname+'.'+
'UHID No:'+convert(va rchar(20),a.reg istrationno)+'. '+
'S/A:'+b.name +'/'+ a.age+'.'+
'Ward:'+d.name+ '.'+
'RNo:'+c.name+' .'+
'Doctor Name:'+f.name as SMSMsg,'IT' as smskey,getdate( ) as smsdate , 0 as status ,'IP' as location
from inpatient as a
inner join sex as b on b.id=a.sex
inner join bed as c on c.ipid=a.ipid
inner join station as d on d.id=c .stationid
inner join ipconsultant as e on e.ipid=a.ipid
inner join doctor as f on f.id=e.doctorid
where f.deleted=0
and a.pphone is not null and a.pphone <> ''
order by a.ipid desc)a
[/code]

[ck9663]

If you really want to take the newly inserted record, use the inserted logical table inside your trigger, not the actual table you just inserted the record to.


Good Luck!!!


~~ CK