Don't Trigger

Is there anyway to not trigger (a trigger) upon a given statement i.e.:
DONT TRIGGER BABY
exec my_SP
thanks
capsch--DONT TRIGGER BABY
That would be a good suggestions for the next syntax release of SQL Server,
but until now you have to disable and enable the trigger again:
ALTER TABLE <SomeTable> DISABLE TRIGGER ALL
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"red" wrote:
> Is there anyway to not trigger (a trigger) upon a given statement i.e.:
> DONT TRIGGER BABY
> exec my_SP
>
> thanks
> capsch
>|||On 10 Aug 2005 01:22:39 -0700, red wrote:
>Is there anyway to not trigger (a trigger) upon a given statement i.e.:
>DONT TRIGGER BABY
>exec my_SP
>
>thanks
>capsch
Hi capsch,
As Jens says: you can use ALTER TABLE to disable trigger execution. But
be aware that this affects all copnnections, not just your connection.
If others are changing data at the same time, the trigger won't fire for
their modifications either!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Exactamondo Hugo thats the problem|||On 10 Aug 2005 06:01:10 -0700, red wrote:
>Exactamondo Hugo thats the problem
Hi red,
Unfortunately, there are no easy workarounds. You'll have to cheat in
some way or another.
One idea is to have the SP add a row in a helper table. Try to read that
row from the trigger, using a READPAST hint. If there's no row there, or
if it's there butinserted from another connection, execute the trigger.
If the row is read, return without doing anything. The downside of this
is that it will serialize execution of the stored procedure, and that
it's quite open to abuse by anyone who knows the trigger code.
You can use a similar trick with an extra column in the table, that can
be set to a "magic value" in the stored procedure.
Another option (but not exactly "safe" either): check @.@.NESTLEVEL to see
if the trigger is called from a stored procedure. Note that this won't
reveal which SP is active - it will only allow you to differentiate
between INSERT, UPDATE, DELETE executed from stored proc, or INSERT,
UPDATE, DELETE executed as a astraight query.
Of course, the main question should be: *WHY* do you want to bypass
trigger execution? Since a trigger is usually designed to maintain
integrity, bypassing it might be very dangerous!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)