[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Update trigger

YaHozna

3/27/2007 11:14:00 PM

Hi. Can someone remind me how to do this?

I have a table on which there is an Update trigger. Works fine. However
there is also a scheduled job that runs a batch update on that table every
week which then also fires the tigger for every row in the table. How do I
prevent this happening and get the trigger to fire only on an update of
individual rows under normal circumstance rather then when the whole table is
updated by the batch?

Regards,

Gordon.
3 Answers

ML

3/28/2007 1:00:00 AM

0

You can disable a trigger (look it up in Books Online), but be careful as
this is a database wide setting.

Follow these guidelines:

1) before starting the batch process make sure no other users can access the
table (you could put the database in single user mode - look it up in Books
Online under LATER TABLE);
2) disable the trigger before you start the batch; and
3) re-enable the trigger after the batch has successfully ended.
4) allow access to database to other users (i.e. put the database back in
multi user mode).


ML

---
http://milambda.blo...

YaHozna

3/28/2007 11:08:00 AM

0

Hi and thanks for the feedback.

Hmmmm. Sounds a bit extreme. Came across another suggestion that involved
checking the number of rows in the update using @@ROWCOUNT. Because, under
normal circumstances, @@ROWCOUNT would be 1 and > 1 for everything else, I
can by-pass the trigger for batch updates. Seems to work.

Regards,

YaHozna.


"ML" wrote:

> You can disable a trigger (look it up in Books Online), but be careful as
> this is a database wide setting.
>
> Follow these guidelines:
>
> 1) before starting the batch process make sure no other users can access the
> table (you could put the database in single user mode - look it up in Books
> Online under LATER TABLE);
> 2) disable the trigger before you start the batch; and
> 3) re-enable the trigger after the batch has successfully ended.
> 4) allow access to database to other users (i.e. put the database back in
> multi user mode).
>
>
> ML
>
> ---
> http://milambda.blo...

ML

3/28/2007 11:20:00 AM

0

What you're suggesting may lead to problems: what about set-based updates?

How about using a table to store "global variables"?
E.g.: prior to the execution of the batch you could set the value of a
column in that special table appropriately and then check that value in the
trigger, rather than preventing set-based operations using @@rowcount.


ML

---
http://milambda.blo...