October 25, 2015 at 8:25 pm
Can I know on what scenario, the disable trigger will be enable again.
Because I already disable the trigger on last month, but when I check through the database, it enable again.
I understand that, when we restore the database, all the trigger will be enable.
How about SQL Cluster? will it enable the trigger??
October 25, 2015 at 8:35 pm
Cluster wont enable the trigger since the database in its exact state is being moved from active to passive. Also with regard to backups I am not sure if the triggers are enabled automatically after restore, I think they will continue in the same state as they were when the backup was taken.
October 26, 2015 at 4:08 pm
I'd suggest not disabling a trigger unless it's absolutely necessary, and it almost never is.
You can instead use CONTEXT_INFO() to pass a flag to the trigger to "tell" it whether to process rows or not for that particular connection.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2015 at 4:38 pm
chris92mars (10/25/2015)
Can I know on what scenario, the disable trigger will be enable again.
Someone ran ALTER TRIGGER ... ENABLE
It should be caught in the default trace, along with the username, so check there and see who ran it.
I understand that, when we restore the database, all the trigger will be enable.
No, if you restore the database, triggers will be in the state that they were at the time the backup was created.
How about SQL Cluster? will it enable the trigger??
No
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply