Enable/Disable Trigger

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
GOWhy 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
"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|||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:
> 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 enabl
e
> 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|||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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx