Insert record to SQL regardless of trigger completion code

Hello,

In my SQL table, I currently have a trigger to send email when a new
record (from MS Access 2000 form) is inserted into the table. It
works fine. The problem is sometimes when Exchange is down or any
problem with email, no email is sent out, and no record is added to
the table.

I would like to add new record when I exit my Access form, regardless
of if Exchange is down or up, or if the trigger runs successfully or
not.

Here is my trigger (simplify):
CREATE TRIGGER trig_Notify
ON dbo.Table1
FOR INSERT
AS
DECLARE @g_user VARCHAR(25)
SET NOCOUNT ON
SELECT @g_user = X.UserName FROM inserted X
EXEC master.dbo.xp_sendmail @recipients = 'test@test.com',
@message = 'test', @subject = Test Notification'

Please help,
JP

re: Insert record to SQL regardless of trigger completion code

hanoi_honai@yahoo.com (James P.) wrote in message news:<f49d1d72.0411021856.8c6a93d@posting.google.c om>...[color=blue]
> Hello,
>
> In my SQL table, I currently have a trigger to send email when a new
> record (from MS Access 2000 form) is inserted into the table. It
> works fine. The problem is sometimes when Exchange is down or any
> problem with email, no email is sent out, and no record is added to
> the table.
>
> I would like to add new record when I exit my Access form, regardless
> of if Exchange is down or up, or if the trigger runs successfully or
> not.
>
> Here is my trigger (simplify):
> CREATE TRIGGER trig_Notify
> ON dbo.Table1
> FOR INSERT
> AS
> DECLARE @g_user VARCHAR(25)
> SET NOCOUNT ON
> SELECT @g_user = X.UserName FROM inserted X
> EXEC master.dbo.xp_sendmail @recipients = 'test@test.com',
> @message = 'test', @subject = Test Notification'
>
> Please help,
> JP[/color]

I found the answer myself: just simply add the "COMMIT TRAN" after the
AS, and before the DECLARE statement. In that way, regardless of the
outcome of the SQL statement running email, my record is still added
to SQL.

JP