By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,827 Members | 2,155 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,827 IT Pros & Developers. It's quick & easy.

Insert record to SQL regardless of trigger completion code

P: n/a
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 = 't***@test.com',
@message = 'test', @subject = Test Notification'

Please help,
JP
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ha*********@yahoo.com (James P.) wrote in message news:<f4*************************@posting.google.c om>...
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 = 't***@test.com',
@message = 'test', @subject = Test Notification'

Please help,
JP


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
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.