Hi,
we use sql 7 and i want to be able have an email sent to a email address whenever an order over ?5000 is placed.
This is what i have the moment, and i do receive an email but i dont know how to show the customer name.
CREATE TRIGGER xackmailcef ON [salesorders]
FOR UPDATE
AS
declare
@xcust char(14),
@xnewflg char(1),
@xoldflg char(1),
@xson integer,
@xcount smallint,
@xcount1 smallint,
@xmess varchar(255),
@cvendor char(14),
@xtotal float,
@xcustname char(40)
select @xcust = scustomer, @xnewflg = sackflg, @xson = son
from inserted
select @xoldflg = sackflg from deleted
if (update(sackflg) and @xnewflg = ‘P’ and @xoldflg = ‘N’) begin
if @xcust LIKE ‘6%’ begin
select @xcount = count(*) from salesitems where sona = @xson and soq>0
if @xcount >0 begin
select @xmess = ‘Order No: ‘+ltrim(convert(char(10),@xson)) + ‘ ‘+ ‘ has been created by MO’ +’ ‘ + char(13)+char(10)+’ ‘+char(10)
exec master.dbo.xp_smtp_sendmail
@server = N’10.21.4.2′, @port = 25,
@FROM = N’sales@XYZ.co.uk’,
@TO = N’mahmad@XYZ.com’,
@subject = N’Order confirmation from MO’,
@message = @xmess
end
end
select @xtotal = sum(sprice) from salesitems where sona = @xson and soq>0
if @xtotal >5000 begin
select @xcustname = custname from wcustomers where cust = @xson
select @xmess = ‘Colleages,
we have just received an order for ‘+ltrim(convert(char(10),@xtotal)) +’ from ‘+ char(13)+char(10)+’ ‘+char(10)
select @xmess = @xmess +char(10) + @xcustname +’ ‘+ char(13)
select @xmess = @xmess +char(10) + ‘Another example of the growth happening in castell! ‘+’ ‘+ char(13)+char(10)+’ ‘+char(10)
select @xmess = @xmess +char(10) + ‘Regards,
TcW ‘+’ ‘+ char(13)+char(10)+’ ‘+char(10)
exec master.dbo.xp_smtp_sendmail
@server = N’10.21.4.2′, @port = 25,
@FROM = N’sales@XYZ.co.uk’,
@TO = N’mahmad@XYZ.com’,
@subject = N’ORDER ALERT’,
@message = @xmess
end
end
——————————-
CHEERS
MO