[RESOLVED] SQL 2008/2005 - Suspend Triggers

[techgnome]

Does anyone know of a way to suspend triggers - without dropping them - from running when executing an insert from T-SQL?

I've got a table that when inserted into fires off a trigger which populates another table. Under normal circumstances I'd be OK with that, but it's messing up later insert statements into that other table, and I need to be able to control the IDs (they are GUIDs so IDENTITY INSERT OFF/ON isn't an option). I need to be able to insert into this table, then the child/related table.

I hate triggers...

-tg

[GaryMazzone]

For all triggers in a database:

--Disable all triggers
EXECUTE sp_msforeachtable "ALTER TABLE ? disable trigger all"
--Enable all triggers
EXECUTE sp_msforeachtable "ALTER TABLE ? enable trigger all"


For 1 table
--Disable
Alter table tablename disable trigger all
--Enable
Alter table tablename enable trigger all

[techgnome]

Gary, that seemed to be what I needed/wanted... Originally I was under the impression that I would be able to do an alter table because of the architecture of the system and the security... but since this is going to be a well-documented one-off script I am creating (to be used as a template on future implementations) and will be run by us as the DBA (as opposed to some of our other scripts which can be run by the client) the permissions will not be a problem. Now I just need to script the data, get the pieces in the right order, and I should be good to go.

thanks!

-tg