SQL Server Performance Forum – Threads Archive

How to hide triggers from SQL Server temporarily?

Hi, I wonder whether there is a way to temporarily hide triggers from SQL Server 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want to do this is because even though I disable triggers, SQL Server will take times to evaluate whether the trigger is disabled or not. Based on my own testing, an alter table statement can be 2 to 3 times faster without trigger comparing to disabled triggers.
Thanks for any help, Peter

AFAIK you can only disable them, not "hide". What about scripting them first, dropping them, do your stuff and then reapply the scripts? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

You want to hide and still that functionality should be there isn’t it?
I don’t think you can do that. —————————————-
http://dineshasanka.blogspot.com/

If you think you dont need triggers them as said script them and drop them Madhivanan Failing to plan is Planning to fail

Frank’s reference is right: alter table <table_name> disable trigger {<trigger_name> | all}
alter table <table_name> enable trigger {<trigger_name> | all}
Well that was in Pre SQL 2005 and in 2k5 referhttp://msdn2.microsoft.com/en-us/ms189748.aspx link.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

I just want to temporarily remove the triggers and then add them back later but these must be done programmatically in T-SQL. Unfortunately, I have not figured out how to do it in T-SQL. Note: Some of the triggers are over 8000 characters long. I think I may able to use Dynamic SQL based on the post from my another thread that I posted. Need to try it out.
Thanks, Peter

]]>