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.  Does
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 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,
Rich

Well, I just tried it out on a test trigger and I was able to disable the
trigger this way.

Show quoteHide quote

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.



Show quoteHide quote

Incorrect - this works on sql server 2000.

Scott Morris" wrote:

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:

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

How can you prevent the data changes message during editing a row
getting dates between start date and end date
how to optimize this procedure
Oracle "Dual" table equivalent in SQL Server
Conditional Where Clause