disable all triggers

I have a startup stored procedure that dumps data from most tables and
repopulates some startup information. I'd like to disable all the triggers
at the start of this procedure and re-enable them at the end. Is there a
simple way to do this other than one disable line per trigger?
Thanks,
KeithIs there any danger in doing this? Is there a better way?
DECLARE
@.sTriggerName AS VARCHAR(200),
@.sTriggersTable AS VARCHAR(200),
@.sSQL AS VARCHAR(1000)
DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT sysobjects.name AS
TriggerName, sysobjects_1.name AS TriggersTable FROM sysobjects INNER JOIN
sysobjects sysobjects_1 ON sysobjects.parent_obj = sysobjects_1.id WHERE
(OBJECTPROPERTY(sysobjects.id, N'IsTrigger') = 1)
OPEN curTriggers
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
@.sTriggerName
EXEC(@.sSQL)
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
END
Keith|||you may want to try this
sp_msforeachtable 'Alter table ? disable trigger all'
"Keith G Hicks" wrote:

> Is there any danger in doing this? Is there a better way?
> DECLARE
> @.sTriggerName AS VARCHAR(200),
> @.sTriggersTable AS VARCHAR(200),
> @.sSQL AS VARCHAR(1000)
> DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT sysobjects.name AS
> TriggerName, sysobjects_1.name AS TriggersTable FROM sysobjects INNER JOIN
> sysobjects sysobjects_1 ON sysobjects.parent_obj = sysobjects_1.id WHERE
> (OBJECTPROPERTY(sysobjects.id, N'IsTrigger') = 1)
> OPEN curTriggers
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
> @.sTriggerName
> EXEC(@.sSQL)
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> END
>
> Keith
>
>|||I can't find sp_msforeachtable in BOL for SQL 2000. I take it it's
undocumented. Anyway, I decided I'd rather only disable triggers that are
currently enabled so as not to enable any that should not be. I'm doing this
first:
CREATE TABLE #EnabledTriggerList (TriggerName VARCHAR(200), TriggerTable
VARCHAR(200))
DECLARE
@.sTriggerName AS VARCHAR(200),
@.sTriggersTable AS VARCHAR(200),
@.sSQL AS VARCHAR(1000)
DECLARE curTriggers INSENSITIVE CURSOR FOR
SELECT name AS TriggerName, OBJECT_NAME(parent_obj) AS TriggerTable
FROM sysobjects
WHERE xtype = 'TR'
AND OBJECTPROPERTY(id,'ExecIsTriggerDisabled
') = 0
OPEN curTriggers
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
@.sTriggerName
EXEC(@.sSQL)
INSERT INTO #EnabledTriggerList (TriggerName, TriggerTable) VALUES
(@.sTriggersTable, @.sTriggerName)
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
END
CLOSE curTriggers
DEALLOCATE curTriggers
-- Then runnning all my table dump and repop code
-- then this
DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT * FROM #EnabledTriggerList
OPEN curTriggers
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' ENABLE TRIGGER ' +
@.sTriggerName
EXEC(@.sSQL)
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
END
CLOSE curTriggers
DEALLOCATE curTriggers
It seems to work fine. Should this be safe?
Thanks,
Keith|||That's the way to do it. sp_msforeach* are undoc/supported and they use
cursor internally anyway.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OvoiKjmQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>I can't find sp_msforeachtable in BOL for SQL 2000. I take it it's
> undocumented. Anyway, I decided I'd rather only disable triggers that are
> currently enabled so as not to enable any that should not be. I'm doing
> this
> first:
> CREATE TABLE #EnabledTriggerList (TriggerName VARCHAR(200), TriggerTable
> VARCHAR(200))
> DECLARE
> @.sTriggerName AS VARCHAR(200),
> @.sTriggersTable AS VARCHAR(200),
> @.sSQL AS VARCHAR(1000)
> DECLARE curTriggers INSENSITIVE CURSOR FOR
> SELECT name AS TriggerName, OBJECT_NAME(parent_obj) AS TriggerTable
> FROM sysobjects
> WHERE xtype = 'TR'
> AND OBJECTPROPERTY(id,'ExecIsTriggerDisabled
') = 0
> OPEN curTriggers
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
> @.sTriggerName
> EXEC(@.sSQL)
> INSERT INTO #EnabledTriggerList (TriggerName, TriggerTable) VALUES
> (@.sTriggersTable, @.sTriggerName)
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> END
> CLOSE curTriggers
> DEALLOCATE curTriggers
> -- Then runnning all my table dump and repop code
> -- then this
> DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT * FROM
> #EnabledTriggerList
> OPEN curTriggers
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' ENABLE TRIGGER ' +
> @.sTriggerName
> EXEC(@.sSQL)
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> END
> CLOSE curTriggers
> DEALLOCATE curTriggers
> It seems to work fine. Should this be safe?
> Thanks,
> Keith
>