Hoping for some advice as the SQL list seems to be full of junk at the moment
I'm trying to build a trigger in SQL 2005 SP2 - code is at the bottom
The trouble is that in a query window with an order number fed in manually (set
@order_no = '086534' for example) the code works fine, but as a trigger @pcode1
and @pcode2 are not getting populated and the code fails
It does actually send an email but the debug text I have put to send me in the
email comes back as @ser_test = '0', @order_no is correct as inserted, but
@pcode1 and 2 are either empty or null
Any pointers welcome as google is not helping at the moment.
Thanks
...Spence
create trigger send_mail
on scheme.opheadm
for insert
as
-- Declare variables that are needed!
declare @order_no char(10)
declare @pcode1 char(32)
declare @pcode2 char(32)
declare @ser_test char(1)
DECLARE @tableHTML NVARCHAR(MAX)
set @ser_test = '0'
-- Get order Number
select @order_no = (select order_no from inserted)
set @pcode1 = (select replace(upper(address5),' ','') as address5 from
scheme.opheadm with (nolock) where order_no = @order_no)
set @pcode2 = (select replace(upper(post_code),' ','') as post_code from
scheme.ophdrpcm with (nolock) where order_no = @order_no)
IF EXISTS (SELECT replace(upper(postcode),' ','') FROM ser_postcodes with
(nolock) WHERE replace(upper(postcode),' ','') = @pcode1)
BEGIN
SET @ser_test = '1'
END
IF EXISTS (SELECT replace(upper(postcode),' ','') FROM ser_postcodes with
(nolock) WHERE replace(upper(postcode),' ','') = @pcode2)
BEGIN
SET @ser_test = '1'
END
if @ser_test = '0'
begin
set @tableHTML = N'' [EMAIL PROTECTED] + Char(13) + Char(10) +
@order_no + Char(13) + Char(10) + @pcode1 + Char(13) + Char(10) + @pcode2
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[EMAIL PROTECTED]',
@subject = 'Invalid Postcode Entered',
@body = @tableHTML,
@body_format = 'TEXT' ;
end
go
~ Finally, powerful endpoint security that ISN'T a resource hog! ~
~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
