Disabling a trigger within a trigger?? 1

[aljubicic]

Hi all,

I would like to disable a trigger within a trigger using....

ALTER TABLE RESIDENTIAL DISABLE TRIGGER UPDATE_SYSTEMS_RES

However it tells me that I cant disable a trigger within one. How can I get around this or is there another way of disabling a trigger.

Thanks
Anthony

 

[JamesLean]

Have you tried creating a stored proc which disables the second trigger and just executing the SP from your first trigger? --James

 

[jmorgan]

Another approach is to create a table that stores trigger names and flags. Example: trigger_name, flag_value. Data would be "my_trigger", 0. When you are in the trigger, simply read from the table and put an "if" condition to see if the trigger should execute based on the flag value. Ex:
Create trigger my_trigger on tablename for insert
AS
declare @flag int
select @flag = flag_value from trigger_table where trigger_name = 'my_trigger'
if @flag = 1
begin
'trigger code here
end

You can update the table from anywhere - to set the flag value depending on a particular condition and/or reset it.

 

[tlbroadbent]

I don't know if a TRIGGER can be disabled from another TRIGGER. Remember that most users will not be able to disable a TRIGGER. Permissions to ALTER a table default to the table owner, DB owner or members of the db_owner or system admin roles.

jmorgan's idea has a lot of merit. All users could be given permission to update the trigger_table under control of a program. I recommend one change to speed the TRIGGER execution.

Create trigger my_trigger on tablename for insert
AS
If Exists
(Select * from trigger_table
Where trigger_name = 'my_trigger'
And flag_value=1)
begin
'trigger code here
end If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm