Enable/Disable Trigger

10 Nov 2005 9:30 PM

Joe K.

I have a stored procedure disable and enable triggers on several tables.

What would be a workaround to using the alter table with disable and enable
triggers listed below.

Thanks,

CREATE PROCEDURE dbo.K_ManualMoves
as
BEGIN
alter table T_Moves
disable trigger iu_t_Moves
alter table T_Fees_Moves
disable trigger iu_t_Fees

Begin
Body Stored Procedure
End

alter table T_Moves
enable trigger iu_t_Moves
alter table T_Fees_Moves
enable trigger iu_t_Fees

End
GO

10 Nov 2005 9:55 PM

Mike John

Why do you need a workaround, if you want to disable the trigger use Alter
table as you have done, Or is there some reason you do not want to disable
the triggers? Without seeing what the rest of the code is it is difficult to
understand your problem

Mike John

Show quote

"Joe K." <Joe K*@discussions.microsoft.com> wrote in message
news:09A5FD91-8F76-4431-B17B-0F4D4FF85B24@microsoft.com...
>
> I have a stored procedure disable and enable triggers on several tables.
>
> What would be a workaround to using the alter table with disable and
> enable
> triggers listed below.
>
> Thanks,
>
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
>
> Begin
> Body Stored Procedure
> End
>
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
>
> End
> GO

10 Nov 2005 10:19 PM

Trey Walpole

That's how to do it.
I'm guessing your concern is that the triggers would be disabled while a
process that should fire them runs. In that case, the most solid option
is to have the triggers manage themselves, based on data being modified
(like a source column indicating what process changed the data), or
environment settings (like user or application) or something else
limited to the scope of the modification.

Joe K. wrote:
Show quote

> I have a stored procedure disable and enable triggers on several tables.
>
> What would be a workaround to using the alter table with disable and enable
> triggers listed below.
>
> Thanks,
>
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
>
> Begin
> Body Stored Procedure
> End
>
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
>
> End
> GO

10 Nov 2005 11:07 PM

Erland Sommarskog

Joe K. (Joe K*@discussions.microsoft.com) writes:

> I have a stored procedure disable and enable triggers on several tables.
>
> What would be a workaround to using the alter table with disable and
> enable triggers listed below.

In the stored procedure create a temp table:

CREATE TABLE #trigger$disabled(a int NOT NULL)

In the trigger you would add

IF object_id('tempdb..#trigger$disabled') IS NOT NULL
RETURN

Even better you can disable only the part that you that you will violate.

--
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