March 6, 2009 at 2:50 am
Hi There,
I would like to request some assistance with an issue I am experiencing with a trigger I have written causing a lock when it fires.
First of all here is a description of what I am trying to achieve...
My user has a requirement to receive an email alert when a value in a table, lets call it ValueB, exceeds another value in the same table, ValueA. To provide this I created an AFTER UPDATE trigger than checks the inserted and deleted psudo-tables and evaluates whether Value A is now greater than Value B in inserted. It also checks that Value A wasn't greater than Value B in deleted, this ensures that the change is a result of the update that has just taken place. Any rows that meet the above condition are copied into another table.
This part of the trigger seems to work fine.
I then added code which uses the system stored procedure sp_send_dbmail to run a query against the table which the rows were copied to and sends the data to the user via email. This code also works fine when run in isolation.
The problem occurs when I try to do all of the above in one operation. It causes a lock with wait type LCK_M_IS.
I have tried putting the two operations described above into separate triggers and also tried put the call to sp_send_mail in a separate stored procedure called by the second trigger but got the same result, a lock which prevents the mail from being sent.
Here is the code of my two triggers, I have ensured that they run in the correct order by running sp_settriggerorder on them.
Trigger 1 (This works)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER trgProjOverBudget ON dbo.SourceTable
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON ;
INSERT INTO dbo.ProjOverBudget (Project, Budget, Spend)
SELECT a.projectcode AS Project,
a.ValueA AS Budget,
a.ValueB AS Spend
FROM inserted a
INNER JOIN deleted b
ON a.UNIQUE = b.UNIQUE
WHERE a.ValueB > a.ValueA-- Spend > Budget
AND a.ValueB > b.ValueB-- Spend value changed during update
AND a.TYPE = 'WJ' ;
END
GO
Trigger 2 (This code also works but not when run in the Trigger)
CREATE TRIGGER trgProjOverBudget_SendMail ON dbo.SourceTable
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM dbo.ProjOverBudget) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProjOverBudget',
@recipients = recipient@mydomain.com;',
@subject = '[NOTIFICATION]: Project has gone over budget',
@body = '[TEST] The following projects have exceeded their budget [TEST]',
@query = 'SELECT * FROM dbo.ProjOverBudget;',
@attach_query_result_as_file = 0,
@importance = 'High'
END
TRUNCATE TABLE dbo.ProjOverBudget
END
GO
Any help or advice will be greatly appreciated.
TIA
Chris King
March 6, 2009 at 3:17 am
Hi Chris
Sending a mail within a trigger seems by design to be a performance issue. Maybe it would be a better solution if you
either:
Write into a mail_to_do table which may be polled by a agent job.
or:
create a sheduled job to send the mail.
In both of this solutions your OLTP database can go-on working without wating since the mail was sent.
Greets
Flo
March 6, 2009 at 3:40 am
Hi,
Thanks for the reply. 🙂
Yes, an SQL Server agent job that runs overnight and polls my results table will work, it will at least provide my user with notifications but ideally I need an event-based solution as the circumstance is one that requires action on the part of the user as quickly as possible during business hours.
Hopefully, there is another way to acheive this that takes the code outside of the second trigger and allows it to execute.
March 6, 2009 at 6:11 am
I am less concerned about using sp_send_dbmail in a trigger in many cases. If you do not have a query attaching or including results, the execution of this procedure simply adds a row onto a service broker queue and then the email message is executed asynchronously by the queue. This makes it pretty much the same thing as writing to a table and having a job poll the table, but the polling is automatic and near real-time.
Unfortunately, you have some weird things happening with inserting into a table, querying the table in your email, and trying to truncate the table. This would be best avoided. The approach in which you write to the table in one trigger and then read from that table and truncate it in a second trigger seems problematic - especially when your mail message is trying to include data from the table you are truncating.
March 6, 2009 at 6:20 am
Thanks for the reply.
I have tried removing the TRUNCATE statement from the second trigger and yet it still causes the same lock when it executes.
I have also tried moving the second trigger to be an AFTER INSERT trigger on the table the results are written to but I still get the same issue.
When I examine the lock, the statement that it is hanging on is the
SELECT * FROM dbo.ProjOverBudget
The lock remains until I kill the process in activity monitor. When this is done the following errors are returned:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Sqlcmd: Error: Microsoft SQL Native Client : Unspecified error.
I think the issue is that the query is running before the write to the table from the 1st trigger has been committed but I can't see a way to avoid this without running the code from a seperate job or process after the event.
March 6, 2009 at 6:25 am
If you can live with near-time solutions, don't go for realtime.
A trigger is executed in transactions scope, hence if your trigger fails, your whole transaction fails !
or:
-- the old way -- SQLAgent
- use an alert to launch a sqlagent job that sends your mail
or:
-- the prefered way -- Service Broker !
- write to an ssb queue, (including the payload you need), and have SSB send the mail for you.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 6, 2009 at 8:52 am
Hi There,
Thanks for you reply.
As per your suggestion I have done a bit of tinkering with Service Broker on our test server.
Although I have successfully created all the componenets Message Type, Queue, Contract etc. I can't see how to send a mail to an email recipent using this feature.
It seems to be for sending messages to queues on databases only.
Am I missing something? Is it possible to send a mail to an email recipient using Service Broker?
Apologies, this is a feature I haven't used before. 🙂
March 6, 2009 at 9:17 am
In your processing procedure for the queue, call sp_db_sendmail.
Of course, this just puts a message into the dbmail queue, so another service broker layer is probably overkill.
March 6, 2009 at 9:24 am
I've found a solution.
Instead of writing out the data to a table, I assign the values to variables and then get the trigger to send the mail without running a query. This is acceptable as the originating transaction will only ever update a single row in the source table.
Here's the final trigger code:
CREATE TRIGGER trgProjOverBudget ON dbo.SourceTable
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @Msg VARCHAR(150)
DECLARE @Project VARCHAR(10)
DECLARE @Budget FLOAT
DECLARE @Spend FLOAT
-- Check if the updated row has a greater spend than budget and is a project
IF EXISTS (SELECT * FROM inserted a WHERE a.ValueB > a.ValueA AND a.WOR_ORDER_TYPE = 'WJ')
BEGIN
-- Set varibles for the Project, Budget and Spend figures
SET @Project = (SELECT ProjectId FROM inserted)
SET @Budget = (SELECT ValueA FROM inserted)
SET @Spend = (SELECT ValueB FROM inserted)
-- Set the message containing the above
SET @Msg = '[TEST] The following project has exceeded budget: '
+ @project + ' Budget: ' + CAST(@Budget AS VARCHAR(10)) + ' Spend: '
+ CAST(@Spend AS VARCHAR(10))
-- Send mail via database mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProjOverBudget',
@recipients = 'Recipient@MyDomain.com',
@subject = '[NOTIFICATION]: Project has gone over budget',
@body = @Msg,
@importance = 'High'
END
END
GO
Thank you all for your help and advice. SQL Service Broker looks interesting and I will be looking into it further but the above code means I can get the user off my back and give myself time to read up on it. 😀
Chris
May 7, 2009 at 12:54 pm
Just something I noticed in your code
IF EXISTS (SELECT * FROM inserted a WHERE a.ValueB > a.ValueA AND a.WOR_ORDER_TYPE = 'WJ')
Even of another totally unrelated field gets altered in the table and value B happens to be greater than value A the trigger would still run and send an email - IE.. even if the user did not change value A or Value B. I usually use
IF update(valueA) OR update(valueB) /* .only run the code if the fields i am interested in were updated...*/
BEGIN
Otherwise you can have this trigger firing like mad sending emails and that can add to your performance issue
regards - mark
----------------------------------------------------
May 15, 2009 at 7:54 am
all of these looks fine, but what if you have multiple rows in inserted table? since this method only sends one (first) inserted row, but in if have multiple inserts together or bulk load, it wouldn't work. what would be the workaroud. it looks like i have tried everything, but it doesn't work. as soon as i put @query in sp_send_dbmail, it hangs.
May 15, 2009 at 4:48 pm
Bulk loads I am quite sure bypass triggers. To use the sp_send_dbmail procedure you have be an admin or the login should be given the securable DatabaseMailUserRole (a schema) pertaining to the database in question.
----------------------------------------------------
May 18, 2009 at 9:01 am
Ok, we can forget about bulk load, but what if i want to have two records inserted together, how would i show both in the mail?
May 18, 2009 at 3:29 pm
Okay I guess I am not clear on your objective. Can I ask how you are inserting records? Without bulk load they are commited one at a time. Do you want to send an email out after every 'n' records get inserted? Why do you need to do this ?
An easy solution is to have a logging table that captures the data you want from the inserted record via a trigger. THen you can run an SQL Job at night that captured data from the that table and emails all the accounts inserted for that day. I hope this helps.
Mark
----------------------------------------------------
May 18, 2009 at 5:59 pm
yes, the job works, but the whole point of my trigger is to e-mail me whenever new record gets inserted. i have cursor in the trigger that goes through several databases. i also have a table that holds logins(usernames) and when i insert new record in the table, my trigger fires, cursor cycles through databases, checks if the username and login exists and if not creates, and then sends me e-mail telling me new login was added. it also does same way for delete and update. so, right now i have it set up that it e-mails me for each login, but if i do
insert into users (UserName, isAdmin)
values ('USERNAME',1)
,(USERNAME,0)
it only sends first one.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply