Re: triggers or code?

On 5 Apr 2006 03:28:13 -0700, Monfu wrote:

>Dear All
>
>To tell you the truth I do not have a lot of experience with SQL
>Server, and am discovering things bit by bit.
>
>The last couple of weeks, I have been reading about triggers.
>
>I have a question.  Are they useful?
>
>I mean, what you can do with Triggers, you can do in code.  For example
>to update an audit table, you can do that in code.  To send emails when
>for example a new customer registers, you can also do that in code.
>
>So my question is, when are Triggers useful?

Hi Monfu,

Yes, they are useful.

If you use a stored proc to code the auditing, someone might find a way
to update the table directly, bypassing the stored proc, and the change
won't be logged. Bypassing a trigger is much, much harder.

Howver, triggers should execute quickly. Never send mail from triggers.
Not only because it slows the execution of your updates, but also
because you're starting non-transactional activities from inside a
transaction. What if your mail is sent, and then an error occurs and the
update is rolled back?
What you can do is write a row to a table that holds mails to be sent; a
seperate process can then be scheduled to periodically check that table
and send the mails that are requested.

--
Hugo Kornelis, SQL Server MVP

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
 news:3m7b3214l6vv860
 dvtqj1kgg89jsgn5ig3@
4ax.com...
> On 5 Apr 2006 03:28:13 -0700, Monfu wrote:
> 
>
> Hi Monfu,
>
> Yes, they are useful.
>
> If you use a stored proc to code the auditing, someone might find a way
> to update the table directly, bypassing the stored proc, and the change
> won't be logged. Bypassing a trigger is much, much harder.
>
> Howver, triggers should execute quickly. Never send mail from triggers.
> Not only because it slows the execution of your updates, but also
> because you're starting non-transactional activities from inside a
> transaction. What if your mail is sent, and then an error occurs and the
> update is rolled back?
> What you can do is write a row to a table that holds mails to be sent; a
> seperate process can then be scheduled to periodically check that table
> and send the mails that are requested.
>

BTW this is exactly what happens with SQL 2005's Database Mail.  A request
goes into a Service Broker queue and the mail is actually sent by a
background process.  Service Broker is transactional so if the transaction
is rolled back, the email request is rolled back as well.

So you absolutely can send email from a trigger in SQL 2005.

David

David,

I am new to 2K5, but what if the service broker queue job finishes before
the actual triggering transactions?  Will the service borker smart enough to
hold up the queued job until the initiating transacation finish first?

I presume this only applies to sp_send_dbmail, and not to xp_sendmail?


"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:O$IIa$cWGHA.128@TK2MSFTNGP05.phx.gbl...
>
> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
>  news:3m7b3214l6vv860
 dvtqj1kgg89jsgn5ig3@
4ax.com... 
>
> BTW this is exactly what happens with SQL 2005's Database Mail.  A request
> goes into a Service Broker queue and the mail is actually sent by a
> background process.  Service Broker is transactional so if the transaction
> is rolled back, the email request is rolled back as well.
>
> So you absolutely can send email from a trigger in SQL 2005.
>
> David
>
>
>

>I presume this only applies to sp_send_dbmail, and not to xp_sendmail?

Yes, xp_sendmail work the same way in 2005 as it did in earlier releases. Se
rvice Broker queuing is
only for Database Mail.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Jan Van der Eecken" <jvandereecken@omam.com> wrote in message
news:eIhvFshWGHA.3660@TK2MSFTNGP04.phx.gbl...
>I presume this only applies to sp_send_dbmail, and not to xp_sendmail?
>
>
> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in mes
sage
> news:O$IIa$cWGHA.128@TK2MSFTNGP05.phx.gbl... 
>
>

And thanks again!

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%238RkwvhWGHA.128@TK2MSFTNGP05.phx.gbl... 
>
> Yes, xp_sendmail work the same way in 2005 as it did in earlier releases.
> Service Broker queuing is only for Database Mail.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Jan Van der Eecken" <jvandereecken@omam.com> wrote in message
> news:eIhvFshWGHA.3660@TK2MSFTNGP04.phx.gbl... 
>

"sqlfan" <sqlfan@discussions.microsoft.com> wrote in message
news:724DD30E-9387-4C3D-B92A- F1B818DD424A@microso
ft.com...
> David,
>
> I am new to 2K5, but what if the service broker queue job finishes before
> the actual triggering transactions?  Will the service borker smart enough
> to
> hold up the queued job until the initiating transacation finish first?

A message in a Service Broker queue (like any row in any table) is never
visible to another session until the transaction that inserted it is
commited.  So when the triggering transaction commits the message will
"appear" in the queue.

David