|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Avoid triggers in conditionI have a insert/update trigger on MYTABLE.
Is it possible to avoid/deactive this trigger under certain conditions? For example, when I run a stored procedure. Any ideas? Maybe setting somekind of temporary variable and unsetting it after the store proc has run? Thanks! Kirsten,
A trigger can be enabled/disabled for a table by using the ALTER TABLE statment. However, TMK, a trigger will always fire when the action that the trigger is defined for occurs and the trigger is enabled. HTH Jerry Show quote "Kirsten" <no***@norep.com> wrote in message news:OEzZ%23j8vFHA.3860@TK2MSFTNGP09.phx.gbl... >I have a insert/update trigger on MYTABLE. > Is it possible to avoid/deactive this trigger under certain conditions? > For > example, when I run a stored procedure. > > Any ideas? Maybe setting somekind of temporary variable and unsetting it > after the store proc has run? > Thanks! > > On Thu, 22 Sep 2005 19:57:20 -0300, Kirsten wrote:
>I have a insert/update trigger on MYTABLE. Hi Kirsten>Is it possible to avoid/deactive this trigger under certain conditions? For >example, when I run a stored procedure. > >Any ideas? Maybe setting somekind of temporary variable and unsetting it >after the store proc has run? >Thanks! > ALTER TABLE TableName DISABLE TRIGGER TriggerName do something ALTER TABLE TableName ENABLE TRIGGER TriggerName Beware that this will disable the trigger for all connections. If a user updates the table just when you are running the script, then the trigger won't fire for him/her either. A better option is to use the trigger only for code that has to be run for in ALL circumstances, and put code that is needed only when end users update the data in the stored procedure they call to make the modifications. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Ok.
What about if the condition is bases on a column? For example, how to ask inside the trigger the following? if only column 4 is updated then do nothing else do everything. Thanks! Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:61e6j19h308utjgsgcm3uios961hlmv896@4ax.com... > On Thu, 22 Sep 2005 19:57:20 -0300, Kirsten wrote: > > >I have a insert/update trigger on MYTABLE. > >Is it possible to avoid/deactive this trigger under certain conditions? For > >example, when I run a stored procedure. > > > >Any ideas? Maybe setting somekind of temporary variable and unsetting it > >after the store proc has run? > >Thanks! > > > > Hi Kirsten > > ALTER TABLE TableName > DISABLE TRIGGER TriggerName > do something > ALTER TABLE TableName > ENABLE TRIGGER TriggerName > > Beware that this will disable the trigger for all connections. If a user > updates the table just when you are running the script, then the trigger > won't fire for him/her either. > > A better option is to use the trigger only for code that has to be run > for in ALL circumstances, and put code that is needed only when end > users update the data in the stored procedure they call to make the > modifications. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) Kirsten,
Use the IF UPDATE (column) AND/OR syntax. See the CREATE TRIGGER statement in SQL BOL. HTH Jerry Show quote "Kirsten" <no***@norep.com> wrote in message news:O$XKZu8vFHA.3252@TK2MSFTNGP10.phx.gbl... > Ok. > What about if the condition is bases on a column? > For example, how to ask inside the trigger the following? > if only column 4 is updated then do nothing > else do everything. > > Thanks! > "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message > news:61e6j19h308utjgsgcm3uios961hlmv896@4ax.com... >> On Thu, 22 Sep 2005 19:57:20 -0300, Kirsten wrote: >> >> >I have a insert/update trigger on MYTABLE. >> >Is it possible to avoid/deactive this trigger under certain conditions? > For >> >example, when I run a stored procedure. >> > >> >Any ideas? Maybe setting somekind of temporary variable and unsetting it >> >after the store proc has run? >> >Thanks! >> > >> >> Hi Kirsten >> >> ALTER TABLE TableName >> DISABLE TRIGGER TriggerName >> do something >> ALTER TABLE TableName >> ENABLE TRIGGER TriggerName >> >> Beware that this will disable the trigger for all connections. If a user >> updates the table just when you are running the script, then the trigger >> won't fire for him/her either. >> >> A better option is to use the trigger only for code that has to be run >> for in ALL circumstances, and put code that is needed only when end >> users update the data in the stored procedure they call to make the >> modifications. >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) > > Look up SET CONTEXT_INFO in BOL.
Show quote "Kirsten" <no***@norep.com> wrote in message news:OEzZ%23j8vFHA.3860@TK2MSFTNGP09.phx.gbl... >I have a insert/update trigger on MYTABLE. > Is it possible to avoid/deactive this trigger under certain conditions? > For > example, when I run a stored procedure. > > Any ideas? Maybe setting somekind of temporary variable and unsetting it > after the store proc has run? > Thanks! > > Maybe this is what you need:
http://www.aspfaq.com/show.asp?id=2016 Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:uOa6xM9vFHA.4032@TK2MSFTNGP15.phx.gbl... > Look up SET CONTEXT_INFO in BOL. > > "Kirsten" <no***@norep.com> wrote in message > news:OEzZ%23j8vFHA.3860@TK2MSFTNGP09.phx.gbl... > >I have a insert/update trigger on MYTABLE. > > Is it possible to avoid/deactive this trigger under certain conditions? > > For > > example, when I run a stored procedure. > > > > Any ideas? Maybe setting somekind of temporary variable and unsetting it > > after the store proc has run? > > Thanks! > > > > > >  |
|||||||||||||||||||||||
Other interesting topics