December 29, 2008 at 8:30 am
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
December 29, 2008 at 9:40 am
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]
December 29, 2008 at 9:44 am
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]
December 29, 2008 at 9:47 am
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]
December 29, 2008 at 12:45 pm
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
December 29, 2008 at 12:52 pm
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?
December 30, 2008 at 7:00 pm
Hi,
:Wow:
I am really amazed by such a overwhelming responses, guidance and more exactly help. 🙂
Thanks you all.
Sree
December 31, 2008 at 8:25 am
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]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply