Alternative to cursor in trigger?

If the update trigger returns an "inserted" table with multiple records,
is there any way to address each record individually without using a
cursor? The code below is my solution using a cursor but the DBA says
no cursors. Thank you for your help.
/* Assume "inserted" table returned multiple records */
OPEN ins_cursor
FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
WHILE @.@.FETCH_STATUS = 0
BEGIN
/* **Pseudo code for sending email to address in each record returned**
xp_sendmail
emailTo = @.emailAddress
emailSubject = @.emailSubject
emailBody = @.emailBody
****************************************
*** */
/* Write log entry for each individual email sent*/
INSERT INTO NotifyLog
(
emailTo, emailSubject, emailBody
)
Values
(
@.emailTo, @.emailSubject, @.emailBody
)
FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
END
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I don't think there is a way to do this as requested without a cursor. The
"bad" part here isn't the word "cursor"... you have to call a stored
procedure for every single row, and whether you use a cursor or some other
fetch mechanism, you're still going to have to do the painful, iterative
approach of looping through each row, one at a time.
My suggestion: Use a scheduled job and perform this kind of row-by-row
activity there (you can mark rows as updated in the trigger, by joining the
real table against inserted on the primary key, and then un-mark each row as
the job sends each e-mail). Surely a five or two-minute interval will be
close enough to real time, without hogging all the performance it takes to
hold the transaction open while all that mail is sent (ugh).
http://www.aspfaq.com/
(Reverse address to reply.)
"Georgia" <xout@.deleted.gov> wrote in message
news:u27zY$cIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> If the update trigger returns an "inserted" table with multiple records,
> is there any way to address each record individually without using a
> cursor? The code below is my solution using a cursor but the DBA says
> no cursors. Thank you for your help.
> /* Assume "inserted" table returned multiple records */
> OPEN ins_cursor
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* **Pseudo code for sending email to address in each record returned**
> xp_sendmail
> emailTo = @.emailAddress
> emailSubject = @.emailSubject
> emailBody = @.emailBody
> ****************************************
*** */
> /* Write log entry for each individual email sent*/
> INSERT INTO NotifyLog
> (
> emailTo, emailSubject, emailBody
> )
> Values
> (
> @.emailTo, @.emailSubject, @.emailBody
> )
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> END
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Sure, use a locally defined Table variable...
Declare @.EMs Table (PKID Integer Primary Key Not Null)
Insert @.EMs (PKID) Select <PrimaryKey> From inserted
Declare @.PKid Integer
While Exists (Select * From @.EMs)
Begin
Select @.PKid = Max(PKID) From @.EMs
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody)
Select emailTo, emailSubject, emailBody
From inserted Where <PrimaryKey> = @.PKid
-- --
Delete @.EMs Where PKID = @.PKiid
End
But why not use a set based statement that "Inserts" the entire set of
records directly from the inserted table into NotifyLog table
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody)
Select emailTo, emailSubject, emailBody
From inserted
"Georgia" wrote:

> If the update trigger returns an "inserted" table with multiple records,
> is there any way to address each record individually without using a
> cursor? The code below is my solution using a cursor but the DBA says
> no cursors. Thank you for your help.
> /* Assume "inserted" table returned multiple records */
> OPEN ins_cursor
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* **Pseudo code for sending email to address in each record returned**
> xp_sendmail
> emailTo = @.emailAddress
> emailSubject = @.emailSubject
> emailBody = @.emailBody
> ****************************************
*** */
> /* Write log entry for each individual email sent*/
> INSERT INTO NotifyLog
> (
> emailTo, emailSubject, emailBody
> )
> Values
> (
> @.emailTo, @.emailSubject, @.emailBody
> )
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> END
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||Don't send email notifications from a trigger. There are a number of
reasons.
1. Email is an inherently asynchronous medium so it is unnecessary and
inefficient to hold a transaction open for the duration of sending
mail.
2. If the trigger is fired inside a transaction that later rolls back
you will have sent a notification for an update that never happened.
3. If the mail server is unavailable or causes a timeout or the
notification process fails for any other reason then either you have to
prevent the update or you have to go ahead without sending a
notification. Do you really want to make the mail server a critical
point of failure for your app?
4. Yes, you'll need a cursor. Not desirable in a trigger.
For these reasons I would second Aaron's suggestion: use some other
process outside a trigger to send notifications.
David Portas
SQL Server MVP
--|||
> Sure, use a locally defined Table variable...
> Declare @.EMs Table (PKID Integer Primary Key Not Null)
> Insert @.EMs (PKID) Select <PrimaryKey> From inserted
> Declare @.PKid Integer
> While Exists (Select * From @.EMs)
> Begin
> Select @.PKid = Max(PKID) From @.EMs
> INSERT INTO NotifyLog
> (emailTo, emailSubject, emailBody)
> Select emailTo, emailSubject, emailBody
> From inserted Where <PrimaryKey> = @.PKid
> -- --
> Delete @.EMs Where PKID = @.PKiid
> End
And for the OP's benefit, this is exactly what I meant by not using an
explicit cursor but still going through the process row-by-row, which is
kind of like a wolf in sheep's clothing. In other words, neither DECLARE
CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have in
a trigger.|||Why Not '
"Aaron [SQL Server MVP]" wrote:

>
> And for the OP's benefit, this is exactly what I meant by not using an
> explicit cursor but still going through the process row-by-row, which is
> kind of like a wolf in sheep's clothing. In other words, neither DECLARE
> CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have i
n
> a trigger.
>
>|||Because he's going to use a cursor, or some other looping mechanism like the
one you've provided, to send mail to each recipient in the inserted table.
If you're asking why that's a bad idea, I take it you don't have much
experience with sending mail from SQL Server, and/or having transactions
wait for and/or depend on it.
http://www.aspfaq.com/
(Reverse address to reply.)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:99713C31-6A95-445E-BB64-4D0EB353A8C6@.microsoft.com...
> Why Not '|||You'd be wrong, as well as impolite... I know about that... and I agree...
but you isaid
In other words, neither DECLARE
CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have in
a trigger.
<<<<<<<<<<<<<<<<<<<<<<
Other than the obvious, (re: looping through the records rather than dealing
with them as a set) Do you have anything to teach me about using such a loo
p
in a trigger?
"Aaron [SQL Server MVP]" wrote:
> Because he's going to use a cursor, or some other looping mechanism like t
he
> one you've provided, to send mail to each recipient in the inserted table.
> If you're asking why that's a bad idea, I take it you don't have much
> experience with sending mail from SQL Server, and/or having transactions
> wait for and/or depend on it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:99713C31-6A95-445E-BB64-4D0EB353A8C6@.microsoft.com...
>
>|||I agree. Use the trigger to post email send requests to another de-coupled
table storing the queue of emails to be sent. Use a second process, or job
to inspect the de-coupled table fro unsent emails and send them from the
second process. This will keep the email process from occurring within the
transaction.
fyi, the Service Broker in Yukon is a perfect queue, but y9ou can accomplish
the same objective in SQL Server 2K.
-Paul Nielsen, SQL Server MVP
www.sqlserverbible.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110063641.901975.135140@.z14g2000cwz.googlegroups.com...
> Don't send email notifications from a trigger. There are a number of
> reasons.
> 1. Email is an inherently asynchronous medium so it is unnecessary and
> inefficient to hold a transaction open for the duration of sending
> mail.
> 2. If the trigger is fired inside a transaction that later rolls back
> you will have sent a notification for an update that never happened.
> 3. If the mail server is unavailable or causes a timeout or the
> notification process fails for any other reason then either you have to
> prevent the update or you have to go ahead without sending a
> notification. Do you really want to make the mail server a critical
> point of failure for your app?
> 4. Yes, you'll need a cursor. Not desirable in a trigger.
> For these reasons I would second Aaron's suggestion: use some other
> process outside a trigger to send notifications.
> --
> David Portas
> SQL Server MVP
> --
>|||
The real problem here is not the cursor, it's sending mail in a trigger.
The mail is not transactional and it will slow down your transactions.
A better approach is to insert all the rows into your NotifyLog with a sent
flag. Then use a job to open a cursor on the NotifyLog for rows with
sent=0. Since its a background job using a cursor is no big deal. The
important thing is that the email will not be sent if the transaction is
rolled back.
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody, sent)
SELECT xxx emailTo, xxx emailSubject, xxx emailBody, 0
Then later
begin transaction
declare @.emailToSend table(id int, emailTo varchar(200) ...)
INSERT INTO @.emailToSend
(id, emailTo, emailSubject, emailBody)
select int, emailTo, emailSubject,emailBody
from NotifyLog (updlock,holdlock)
where sent = 0
update NotifyLog set sent = 1
where id in (select id from @.emailToSend)
commit transaction
//send all the emails
David