I am using SQL Mail to send emails to several users when a new Customer Complaint record is INSERTed into a SQL Server 2000 table.

CURRENT CODE:

create trigger tr_NewCustServiceInquiry on table1 for insert as

   declare @vmessage varchar(500)
   declare @vsubject varchar(100)

   select @vmessage = 'Customer Service Quality Alert ' + char(13) + char(13) +
                      'Inquiry Number: ' + s.fcInqNo + char(13) +
                      'Customer Number: ' + s.fcCustNo + char(13) +
                      'Customer: ' + s.fcCustomer + char(13) +
                      'Part Number: ' + s.fcPartno + ' Rev: ' + s.fcPartRev + char(13) +
                      'Quantity: ' + CAST(s.fnqty AS varchar(20)) + CHAR(13) +
                      'Sales Order # ' + s.fcSono + char(13) +
                      'Problem Category: ' + p.fcpoptext,
          @vsubject = 'Quality Alert - Customer Service - ' + x.fcpoptext
   from inserted s INNER JOIN
        cspopup p ON p.fcpopkey = 'SYCSLM.FCCATEGORY' AND s.fccategory = p.fcpopval INNER JOIN
        cspopup x ON x.fcpopkey = 'SYCSLM.FCSEVERITY' AND s.fcseverity = x.fcpopval

   exec master..xp_startmail

   exec master..xp_sendmail
          @recipients = '[email protected];[email protected]',
          @blind_copy_recipients = '[email protected]',
          @subject = @vsubject,
      @message = @vmessage

   exec master..xp_stopmail

The INSERT email Trigger works great and everything runs well - normally.

But, if the Exchange Server is down or it has some other glitch that prevents the email Trigger from running properly, an error is raised in the Trigger and the entire transaction is ROLLedBACK - including the INSERTED record.

I want the Trigger to fail without a ROLLBACK of the INSERT.
I read about XACT_ABORT and I am wondering if this will work...

NEW CODE:

create trigger tr_NewCustServiceInquiry on table1 for insert as

   declare @vmessage varchar(500)
   declare @vsubject varchar(100)

set xact_abort off
go

begin transaction
   select @vmessage = 'Customer Service Quality Alert ' + char(13) + char(13) +
                      'Inquiry Number: ' + s.fcInqNo + char(13) +
                      'Customer Number: ' + s.fcCustNo + char(13) +
                      'Customer: ' + s.fcCustomer + char(13) +
                      'Part Number: ' + s.fcPartno + ' Rev: ' + s.fcPartRev + char(13) +
                      'Quantity: ' + CAST(s.fnqty AS varchar(20)) + CHAR(13) +
                      'Sales Order # ' + s.fcSono + char(13) +
                      'Problem Category: ' + p.fcpoptext,
          @vsubject = 'Quality Alert - Customer Service - ' + x.fcpoptext
   from inserted s INNER JOIN
        cspopup p ON p.fcpopkey = 'SYCSLM.FCCATEGORY' AND s.fccategory = p.fcpopval INNER JOIN
        cspopup x ON x.fcpopkey = 'SYCSLM.FCSEVERITY' AND s.fcseverity = x.fcpopval

   exec master..xp_startmail

   exec master..xp_sendmail
          @recipients = '[email protected];[email protected]',
          @blind_copy_recipients = '[email protected]',
          @subject = @vsubject,
      @message = @vmessage

   exec master..xp_stopmail

commit transaction
go
set xact_abort on
go
return

Will this work? Any ideas?

I don't think it'll work. As you know, the trigger and statement that fires it are treated as a SINGLE transaction. Trigger fails - the whole transaction fails.
I would suggest this:
create trigger mytrigger on table1
for insert
as
exec MyStoredProcedure

and have stored procedure to send your notification.
I hope that will work. Good luck.