Sunday, February 26, 2012
Enable/Disable Trigger
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
Sunday, February 19, 2012
enable a trigger
I know that you can enable/disable a trigger through the alter
table command. Once a trigger is disabled, is there a way
to determine whether it is disabled or enabled? I see there is a
status column in sysobjects ...is that what I should be using?
thanks in advance for any help
KarenKaren (kjphipps_357@.hotmail.com) writes:
> I know that you can enable/disable a trigger through the alter
> table command. Once a trigger is disabled, is there a way
> to determine whether it is disabled or enabled? I see there is a
> status column in sysobjects ...is that what I should be using?
> thanks in advance for any help
No. You should use objectproperty(id, 'ExecTriggerIsDisabled')
Note: I typed property from memory. Look up objectproperty in Books
Online to verify that I got it right.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp