WL#2825: Triggers: enable or disable

Allow alteration of triggers so that they 
are not activated during data-change statements. 
Syntax: 
ALTER TRIGGER ... ENABLE | DISABLE 
ALTER TABLE ... ENABLE | DISABLE ALL TRIGGERS 

It might be occasionally convenient to disable a     
trigger, that is, to prevent it from being activated     
during data-manipulation language statements. The     
disabling could be temporary, so there must be a way     
to re-enable the trigger later. This is a non-standard     
feature but other vendors support it.     
    
Syntax     
------     
     
ALTER TRIGGER trigger_name     
 { ENABLE | DISABLE }     
     
ALTER TABLE table_name     
 [ { ENABLE | DISABLE } ALL TRIGGERS ]     
     
Examples:     
     
ALTER TRIGGER t_ai ENABLE;     
ALTER TRIGGER t_bu DISABLE;     
ALTER TABLE t ENABLE ALL TRIGGERS;     
ALTER TABLE t DISABLE ALL TRIGGERS;     
     
The above is close to Oracle syntax.     
Alternatives were:     
     
(Sybase) "ALTER TABLE t DISABLE TRIGGER".     
When you don't specify a trigger_name, that     
means "all triggers".     
     
(SQL Server 2000) "ALTER TABLE t ENABLE TRIGGER     
t_ai" is the same as "ALTER TRIGGER t_ai     
ENABLE". "ALTER TABLE t ENABLE TRIGGER ALL"     
is the same as "ALTER TABLE t ENABLE ALL     
TRIGGERS". Microsoft also has:     
"ALTER TRIGGER ... NOT FOR REPLICATION".     
     
{ SQL Server 2005}      
DISABLE | ENABLE TRIGGER     
{     
 [ schema_name ] . [ trigger_name ] | ALL ]     
 ON     
 { object_name | DATABASE | ALL SERVER }     
     
(PostgreSQL) Startup with --disable-triggers     
is possible, but there's no "ALTER TRIGGER     
table_name ENABLE|DISABLE".    

Peter Gulutzan rejected all the above alternatives,
but any MySQL worker can vote on a raw idea.   
  
Using SET statement  
------------------- 
  
Sergei Golubchik has proposed that we use  
SET @@disable_triggers = 1;  

The following are Peter's guesses, not from Sergei.  

There is only a "session" setting. We  
would have to wait for locks if we had  
a "global" setting. (Peter assumes that ALTER  
TRIGGER waits for locks, because DROP TRIGGER  
does.)  
  
Since there is only a "session" setting, there  
is no need for a special privilege. 

Konstantin Osipov objects that SUPER privilege
should still be required as disabling triggers may
help a user circumvent audit logging of table
updates.

Mark Leith agrees with Konstantin - unless this is 
completed after a full audit logging capability 
has already been added to the server. 
  
The initial value of @@disable_triggers is  
0 (false), and one can reset to that with  
SET @@disable_triggers = 0;  
Or you can start with --disable-triggers, as  
in PostgreSQL.  
  
The setting is replicated.  
  
The setting is ignored if it occurs while  
a trigger is being processed.  
  
If we support "SET" now, we don't have to do  
"ALTER TRIGGER" now, since the urgent thing  
is a replication requirement. But ALTER TRIGGER  
is probably something we'll have to do at some  
time anyway.  
  
Voting so far:  
Peter says: do ALTER  
Sergei says: do SET  
Elliot says: do SET  
Konstantin says: do SET
Mark Leith says: do SET
Other votes from MySQL workers are welcome, before
architecture review.  
     
Effect     
------     
     
After successful ALTER ... DISABLE, the trigger     
is disabled forever, or until the next ALTER     
... ENABLE occurs. A disabled trigger continues     
to exist and is visible, but is never activated     
by statements like insert, update, or delete.     
     
If a trigger is already disabled, ALTER ... DISABLE     
has no effect and there is no error or warning.     
If a trigger is already enabled, ALTER ... ENABLE     
has no effect and there is no error or warning.     
     
Privileges     
----------     
     
You need SUPER privilege to enable or disable     
triggers. You do not need any ALTER privilege.     
     
Metadata     
--------     
     
We need a new column in information_schema.triggers:     
STATUS CHAR(8)     
Possible values: 'DISABLED' or 'ENABLED '.     
Peter failed to find an equivalent in SQL Server.     
    
CREATE TRIGGER    
--------------    
    
Somebody might want to formulate a CREATE    
TRIGGER statement based on the metadata,    
while the trigger is disabled. So do we need    
to accept [ ENABLED | DISABLED ] somewhere    
in the CREATE TRIGGER statement? Peter supposes so.    
He doesn't care where. The default is ENABLED.    
But with this, there will be a way to create    
a trigger which is initially disabled.    
     
Things that we will not do     
--------------------------     
     
There is no database-wide ENABLE|DISABLE statement.     
     
There is no way to disable or enable triggers     
for all sessions, for a particular session,     
for a transaction, within a stored procedure,     
or during replication.     
     
There is no plan for enabling and disabling     
DDL triggers (WL#2418).    
    
References     
----------     
     
Oracle ALTER TRIGGER statement:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_4001.htm#sthref4060

Oracle 11g trigger "Create a disabled trigger" example:
http://www.nyoug.org/Presentations/2006/200612_Winter_Meeting/Kyte_PL-SQL%20Enhancements.pdf

SQL Server 2005 ALTER TABLE statement:     
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp

SQL Server 2005 DISABLE TRIGGER statement:
http://msdn2.microsoft.com/en-us/library/ms189748.aspx

MySQL forum where somebody asked about enable/disable:     
http://forums.mysql.com/read.php?99,40139     

Feature requests:
BUG#14661 ALTER TRIGGER trigger_name DISABLE or ALTER TABLE table_name DISABLE
TRIGGERS