July 22, 2009 at 5:56 am
Hello,
Hopefully someone here can put me on the right path.
I have a small, ~10 column table with 80 records in it. I need to put a DML trigger on this table to fire AFTER INSERT, UPDATE, DELETE. This trigger should pick up the data that was just modified and email it as an attachment.
I have tried doing this using sp_send_dbmail. My problem is mutal exclusion deadlock. Because (as I understand) sp_send_dbmail runs in a seperate session, I cannot use 'inserted' or 'deleted' in the @query sproc parameter. If I try and insert the (ins, del) values from the trigger in a temporary table or table variable, sp_send_dbmail cannot find this table. If I use a permanent table, e.g. inserting into it the updated values from the trigger and then querying it for sp_send_dbmail, then a mutual exclusion deadlock occurs, where the INSERT statement is waiting for the trigger to finish, and vice versa.
I tried ending the DML trigger with the INSERT statement into the permanent table, and then using another trigger on the permanent table to send an email on INSERT. This still experiences the same deadlock 🙁
Please help!
July 22, 2009 at 6:27 am
I can't test this but off the top of my head you could try something like this:
begin transaction tran1
build an insert string
begin transaction tran2
create your table
exec insert string
commit tran2
send email
commit tran1
I know that triggers assume an @@trancount of 1 when they start and this could be your problem because transactions are actually committed until the @@trancount = 0. If none of this works why not set up the trigger to populate a real table and then have a job run every minute to send an email with all data in that table.
July 22, 2009 at 10:00 am
Hi,
I briefly managed to fudge this by creating a temporary table, using a SQL Agent job to create an email attachment file using the temp table and then attaching the file in sp_send_dbmail. There was also a WAITFOR DELAY of 20 seconds after the SQL Agent job started to give it time to complete.
This worked for an hour, before inexplicably changing behaviour. Now the SQL Agent job seems to begin after the WAITFOR, when it did not previously. Since the trigger does not take GO statements, it must be a quirk of the SQL execution engine.
Anyway, thanks for your suggestion Matt. I will try it tomorrow.
July 22, 2009 at 10:27 am
http://vinay-thakur.spaces.live.com/blog/cns!645E3FC14D5130F2!208.entry
HTH.
Vinay
*Nothing is easy...
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply