Cancel Trigger

24 Nov 2005 10:08 PM

Serkan Ahtagil

i have a trigger on a table it is working good, but sometimes i need to copy
a large amount of data to that table and i want to disable trigger only for
that transaction. Ýs there any easy way to do this?

24 Nov 2005 10:12 PM

Stijn Verrept

Serkan Ahtagil wrote:

> i have a trigger on a table it is working good, but sometimes i need
> to copy a large amount of data to that table and i want to disable
> trigger only for that transaction. ]s there any easy way to do this?

Yup:

DISABLE TRIGGER { [ schema . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

--

HTH,

Stijn Verrept.

24 Nov 2005 10:27 PM

David Portas

"Serkan Ahtagil" <ser***@cpmsoft.com.tr> wrote in message
news:uTNLQOU8FHA.3660@TK2MSFTNGP09.phx.gbl...
>i have a trigger on a table it is working good, but sometimes i need to
>copy a large amount of data to that table and i want to disable trigger
>only for that transaction. Ýs there any easy way to do this?
>

ALTER TABLE ... DISABLE TRIGGER

but this is highly undesirable. If you do it outside a transaction then
every user will be affected. If you do it in a transaction then you'll block
other users.

In my view if your trigger logic doesn't apply to every transaction in a
live system then it has no business being in a trigger. That's what store
procs are for.

--
David Portas
SQL Server MVP
--

24 Nov 2005 10:35 PM

Hugo Kornelis

On Fri, 25 Nov 2005 00:08:11 +0200, Serkan Ahtagil wrote:

>i have a trigger on a table it is working good, but sometimes i need to copy
>a large amount of data to that table and i want to disable trigger only for
>that transaction. Ýs there any easy way to do this?
>

Hi Serkan,

No.

You can run ALTER TABLE <tablename> DISABLE TRIGGER <triggername | ALL>
to disable the trigger for all connections.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

24 Nov 2005 11:10 PM

Erland Sommarskog

Serkan Ahtagil (ser***@cpmsoft.com.tr) writes:

> i have a trigger on a table it is working good, but sometimes i need to
> copy a large amount of data to that table and i want to disable trigger
> only for that transaction. Ýs there any easy way to do this?

ALTER TABLE DISABLE TRIGGER turns off the trigger for everyone, which
may not be desireable.

Turning off triggers should not be done lightly. Triggers are there
to enforce business rules by checking or cascading updates. Thus, there
is rarely a reason to disable a trigger, unless you are into something
really special.

There is an alternative way of turning off a trigger, or a part of it,
only for a connection. The trigger could include this code:

   IF obejct_id('tempdb..#trigger$disabled') IS NULL
   BEGIN
      ... trigger stuff here

The process that wants to sneak by can then create this temp table.
There is no need to have any data in it. The mere existence serves as a
flag.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

25 Nov 2005 11:44 AM

Serkan Ahtagil

thank u for all replies, i will go on this way

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns971917842B7EYazorman@127.0.0.1...
> Serkan Ahtagil (ser***@cpmsoft.com.tr) writes:
>> i have a trigger on a table it is working good, but sometimes i need to
>> copy a large amount of data to that table and i want to disable trigger
>> only for that transaction. Ýs there any easy way to do this?
>
> ALTER TABLE DISABLE TRIGGER turns off the trigger for everyone, which
> may not be desireable.
>
> Turning off triggers should not be done lightly. Triggers are there
> to enforce business rules by checking or cascading updates. Thus, there
> is rarely a reason to disable a trigger, unless you are into something
> really special.
>
> There is an alternative way of turning off a trigger, or a part of it,
> only for a connection. The trigger could include this code:
>
>   IF obejct_id('tempdb..#trigger$disabled') IS NULL
>   BEGIN
>      ... trigger stuff here
>
> The process that wants to sneak by can then create this temp table.
> There is no need to have any data in it. The mere existence serves as a
> flag.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx