Transaction within a AFTER INSERT trigger

Question

Transaction within a AFTER INSERT trigger

S-322532

SSCommitted

Points: 1613
More actions
December 29, 2008 at 8:30 am

#383649

Hi,
I wrote the following code:
create trigger triggerMail
on tbl_booking after insert
as
begin
declare @status varchar(1)
select @status = Column_Status from inserted
if @status = 'Y'
xp_sendmail @recipients, @subject
end
since its a after insert trigger is it possible to rollback the entire transaction ( inserting new rows i.e. undo the 'just happened insertion' ). For ex: if the Column_Status a mail should be sent at any case otherwise the entire transaction should be rolled-back. If so, can anyone state the ways to achieve it ?
Note: insertion is taken care by a stored procedure. requirement is to enforce mailing activity in a trigger.
Thanks
Sree

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I think that all you have to do is execute a "ROLLBACK TRAN" in the trigger.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

By the way, "xp_sendmail" uses SQL Server Mail. You should be using Database Mail instead. You definitely do not want to be using SQL Server Mail from within a trigger as it is synchronous and can easily hang your users and other applications.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

OK, I found it. "sp_send_dbmail" is the equivalent procedure for Database Mail.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

Everything Barry said, plus, your trigger will only correctly deal with a single row insert. If any process does a multi-row insert your trigger will not handle it correctly.

If you are only doing inserts through the stored procedure, I would recommend doing the checking you are doing in the trigger in the stored procedure. Why do the insert and then roll it back if the column_status <> 'Y'? Wouldn't it make sense to check the column_status value in the stored procedure and just not do an insert if it is not 'Y' and send the email, after the insert is completed if it is 'Y'. Something like this in the SP:

If @column_status = 'Y'

Begin

Begin Try

Begin Transaction

Insert Into tbl_booking

(

columns

)

Values

(

value list

)

Commit Transaction

Exec sp_send_dbmail @recipients = @recipients, @subject = @subject

End Try

Begin Catch

IF @@TRANCOUNT > 0

Begin

RollBack Transaction

End

Select

Error_Number() As ErrorNumber,

ERROR_MESSAGE() as ErrorMessage

End Catch

End

Else

Begin

--message to send to calling program

RaisError('Booking row not created because of invalid status', 16, 1)

End

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Or - make this an INSTEAD OF trigger and check the stuff BEFORE you insert it. that way again, no rollback (since nothing has happened yet). Instead - you only issue the insert on the stuff that passes your test, and skip the rest.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

S-322532 SSCommitted Points: 1613 More actions · Answer 6

Hi,

:Wow:

I am really amazed by such a overwhelming responses, guidance and more exactly help. 🙂

Thanks you all.

Sree

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]