This simply makes the whole UPDATE/INSERT not work. When I remove the cursor declaration, it works. The cursor is just selecting a field from a table that is existing in the same database called "contacts". What is wrong?
A trigger should ideally do something very fast, so that it can run "incognito". If what you want is to loop through a child table and send a mail for each row, I agree with the suggestion to change the trigger to instead just update a table with the information that should be e-mailed, and have a job run every n seconds/minutes/hours/whateverintervalyouwant.
Here's how I would change your current trigger.
First a table to store the information to send:
And then some changes to the trigger:
Finally I would create a stored procedure which actually sends the emails:
An alternative to this would be to include the logic to send e-mails into the original stored procedure which performs the updates. But that would mean the application has to wait for an e-mail to be sent for each child-row, and that's not very efficient either.
My suggestion is written totally without even the simplest compilation tests, so it probably needs a few tweaks...
answered Jul 29, 2010 at 03:40 AM
I'm not sure, entirely, but I have a feeling it's SQL Server's way of telling you that a cursor inside a trigger is not a good way to go.
Triggers, ok (some people will try and avoid them like the plague, but I accept that they have their place) - but a cursor within a trigger is going to be a performance issue.
Try and do what you wanted to do in a set-based manner, rather than using the cursor.
answered Jul 29, 2010 at 12:16 AM
Matt Whitfield ♦♦