Disable Trigger Question

Here is a trigger in a table that I am working on. Call it tbl1. AT the
bottom of the trigger is an Alter Table statement which looks like it
disables the trigger. My question is for the purpose of this Alter Table
statement at the end of the trigger. I am not familiar with the logic. Doe
s
this run the trigger and then disable it? Or does this statement disable th
e
trigger altogether? If the trigger runs and then gets disabled, what would
be the purpose of doing that?
---
CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
For INSERT, UPDATE
AS
update tbl1
set CoID = c.CoID
from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
where i.CoID <> c.CoID
alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
----
Thanks,
RichWell, I just tried it out on a test trigger and I was able to disable the
trigger this way.
"Rich" wrote:

> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||It looks as if somebody was trying to "manually" avoid what is known as
trigger recursion: a trigger repeatedly calling itself. The trigger modifies
the table that it is defined on, so you would think that it would keep
calling itself in an infinite loop.
SQL server has options for trigger recursion (triggers making changes that
cause themselves to fire again), and nesting (triggers making changes that
fire other triggers). In SQL 2000, trigger recursion is disabled by default.
Even with it enabled, a trigger would call itself a maximum of 32 times.
Recursive triggers are enabled or disabled with ALTER DATABASE .
In SQL 2000, that trigger wouldn't have worked anyway, because DDL
statements were not permitted in triggers. In SQL 2005, they are.
"Rich" wrote:

> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||> In SQL 2000, that trigger wouldn't have worked anyway, because DDL
> statements were not permitted in triggers. In SQL 2005, they are.
Incorrect - this works on sql server 2000.|||Scott Morris" wrote:

> Incorrect - this works on sql server 2000.
>
I stand corrected; however, what's the point of a one-time trigger? If
trigger recursion was even enabled in the first place, wouldn't it be much
better to accomplish the desired action by checking @.@.NESTLEVEL?|||> I stand corrected; however, what's the point of a one-time trigger? If
> trigger recursion was even enabled in the first place, wouldn't it be much
> better to accomplish the desired action by checking @.@.NESTLEVEL?
Indeed - what is the point of such a trigger! That would be a question for
the original programmer. I would fire someone in my employ that did such a
thing - especially since there is no comment in the code to indicate the
reason.|||Create the table and the trigger, look at the trigger code, then
disable the trigger and look at the trigger code again
surprise!!!! last line in the trigger code is "alter table
[dbo].[TestTrigger] disable trigger [trTest]"
So this is not a one time thing, this happens when you disable a
trigger, run code provided to test it out
CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )
INSERT INTO TestTrigger
SELECT 'SQL',500.23
GO
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @.@.ROWCOUNT =0
RETURN
IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO
alter table TestTrigger
disable trigger trTest
Look at the trigger code again
BTW
sp_helptext trTest won't show this, you have to do it from manage
triggers after you right click on the table
http://sqlservercode.blogspot.com/|||On Thu, 2 Feb 2006 11:04:58 -0800, Rich wrote:

>Here is a trigger in a table that I am working on. Call it tbl1. AT the
>bottom of the trigger is an Alter Table statement which looks like it
>disables the trigger. My question is for the purpose of this Alter Table
>statement at the end of the trigger.
Hi Rich,
None - having a trigger that runs only once, then disables itself has no
purpose at all.
The script posted by "SQL" reveals the bug that caused this alter table
statement to be in the trigger.
If you use Enterprise Manager to manage a trigger (right-click a table;
select All Tasks / Manage Triggers), you'll get a window in which is
some generated code that will recreate a trigger in it's current state.
For a normal trigger, that would be:
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
And if the trigger is currently disabled, that should be extended to
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
GO
ALTER TABLE table DISABLE TRIGGER name
Unfortunately, there seems to be a bug in Enterpris Manager - it omits
the batch seperator ("GO") in the generated code. That makes a huge
difference. With the GO, the trigger gets recreated in it's original
form, then (in a seperate batch) disabled. Without the GO, the trigger
gets recreated with an extra ALTER TABLE statement tacked on at the end
of the code, and is kept in an enabled state. Of course, after being
fired once, the trigger will be disabled again, due to the execution of
the extra ALTER TABLE statement.
It really gets funny if you repeat the exercise a few times. Starting
from the code posted by "SQL", I have now a trigger with this code:
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
(snip)
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
(I omitted the blank lines to keep the post readable).
To fix this, take the following steps:
1. Find out if the trigger needs to be enabled or disabled in your
applications. If disabled, you might want to check if it will ever be
needed again.
2. Copy the current code of the trigger into Query Analyzer, remove the
ALTER TABLE statement and recreate the trigger. Then, if necessary,
disable it. (Doon't forget to add a GO statement if you intend to do
this all in one script <g> ).
3. Make a mental note to never ever use Enterprise Manager again for
creating or changing triggers. (Same goes for lots of other tasks too;
EM is really only a DBA tool; development work should be done in Query
Analyzer).
Hugo Kornelis, SQL Server MVP