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
You must be logged in to tag this worklog