But also sometimes you have to DISABLE TRIGGER for some maitenance tasks when you have to fix some data.
But disabling trigger has global effect. At this time no user is protected by entering wrong data into DB.
You may implement some exceptions into blocking trigger.
First (obvious) idea is to make an exception for user logged into SQL Server:
IF SUSER_SNAME() IN ('superuser1', 'DOMAIN\superuser2') RETURN
And RETURN escapes from blocking trigger at its beginning.
If you are "superuser1" you are not protected by trigger at all. And you want to do maintenance only ad-hoc.
Let me introduce CONTEXT_INFO solution.
CONTEXT_INFO is a "key" set to current SQL connection - so it's ad-hoc setting. It gets VARBINARY(128) as value.
Simplest example (from MSDN) is
SET CONTEXT_INFO 0x1256698456
But who's gonna to remember 0x1256698456 as his very special "key"?
My way - use varchar "key" this way:
DECLARE @my_god_mode VARBINARY(128) = CAST('MY_GOD_MODE' AS VARBINARY(128))
SET CONTEXT_INFO @my_god_mode
If you put (above) into your maintenance script and (following) into blocking trigger- you have a complete solution:
IF CONTEXT_INFO() = CAST('MY_GOD_MODE' AS VARBINARY(128)) RETURN
or in more secret way.. :p
IF CONTEXT_INFO() = 0x4D595F474F445F4D4F4445 RETURN
Last tip - conversions both ways to play:
SELECT CAST('MY_GOD_MODE' AS VARBINARY(128))
SELECT CAST(0x4D595F474F445F4D4F4445 AS VARCHAR)
Have a nice god modding, guys! :)