How to hide triggers from SQL Server temporarily? | SQL Server Performance Forums

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
Heute schon gebloggt?
You want to hide and still that functionality should be there isn’t it?
I don’t think you can do that. —————————————-

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 refer link.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
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