Allow CREATE TRIGGER for DDL, i.e. CREATE/ALTER/DROP statements.
This is non-standard and Peter Gulutzan would be happy if we did this sort
of thing with CREATE EVENT (see later section "Events").
But since DDL triggers are legal for Oracle11g and
SQL Server 2008, we might get requests for them.
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER
{ BEFORE | AFTER }
{ALTER|COMMENT|CREATE|DROP|GRANT|RENAME|REVOKE | DDL}
ON
[ FOR EACH STATEMENT ] /* does nothing. we might skip this. */
This is more like Oracle than like SQL Server (SQL Server would
allow event-object name combinations like ALTER_TABLE or DROP_TABLE).
We don't allow an event list like "BEFORE ALTER, COMMENT, CREATE ..."
but we do allow "DDL" which means "all possible data-definition-language
events".
The "ON " clause may be
"ON database7.*" or "ON *" (all objects in database) or
"ON t1" (all objects, e.g. tables or indexes or procedures, named t1)
We do not allow "ON *.*" (all objects in all databases) because
triggers are schema objects so they are stored in a single database.
(Oracle doesn't have this restriction.)
The trigger applies only for objects in the database, not for
the database itself. That is, there is no trigger for CREATE
DATABASE, DROP DATABASE, etc. (Oracle doesn't have this restriction.)
You need a trigger privilege for the object in the same way that you
need a trigger privilege for insert/update/delete on the object.
For example, if you say "CREATE TRIGGER t_bg BEFORE GRANT ON t ..."
you must have TRIGGER privilege on table t.
For example, if you say "CREATE TRIGGER t_bc BEFORE CREATE ON d1.t ..."
you must have TRIGGER privilege on "d1.*", that is, on the database.
The triggered SQL statement may not contain references to NEW or OLD values.
The triggered SQL statement may not be, or contain, or cause,
any DDL statement. (I suggest this restriction to make the task easier.
But it's up to the implementor to decide.) Therefore we do not expect
cascade effects. For example this infinite-cascade method is illegal:
CREATE TRIGGER t1 AFTER DROP ON db1.* CREATE TABLE t1 (s1 INT);
CREATE TRIGGER t2 AFTER CREATE ON db1.* DROP TABLE t1;
Other restrictions for the "ON clause" and the triggered SQL statement are
the same as for insert/update/delete triggers, as noted in WL#1218 Triggers.
The statement for dropping the trigger is unchanged:
DROP TRIGGER trigger_name
Effect
------
Just before or just after an object is changed due to a DDL statement,
the trigger is activated. If it's a BEFORE trigger, then it does not
matter if the statement eventually fails.
If a single statement refers to multiple objects, as with "DROP TABLE
t1, t2, t3;", the trigger is activated only once. All DDL triggers
are FOR EACH STATEMENT, none are "for each object".
If the triggering statement is RENAME or ALTER ... RENAME, then
the trigger's object name depends on the { BEFORE | AFTER } clause.
For example:
CREATE TRIGGER t1_ar AFTER RENAME ON t1 SET @a=5;
RENAME t1 TO t2;
This RENAME does not cause activation of trigger t1_ar, because
it's an AFTER trigger, and AFTER the rename the object is t2,
not t1.
If the triggering statement is CREATE TEMPORARY ..., then
all CREATE triggers are activated. (SQL Server has a restriction
that temporary-table actions don't cause trigger activations.)
If the triggering statement is CREATE|ALTER|DROP USER, then
no triggers are activated. A user is not an object in a database.
If the triggering statement is INSTALL|UNINSTALL PLUGIN, then
no triggers are activated. Plugin statements are not considered
to be DDL.
If the triggering statement is ANALYZE, then
no triggers are activated. (Oracle would consider ANALYZE
to be DDL; we don't.)
TRUNCATE
--------
Mark Leith's comment:
"
Why do we not want to add TRUNCATE to this specification as well?
I know that Oracle does not support DDL triggers with TRUNCATE, but I feel this
would be a valid addition as well. I'm not sure why "we don't consider TRUNCATE
to be DDL" - what then do we class it as?
Given that TRUNCATE *should not* cause DELETE triggers to fire as well, it would
still be nice to capture these actions. I say should not, as actually TRUNCATE
does currently fire DELETE triggers with InnoDB (within 5.0.30):
http://bugs.mysql.com/bug.php?id=25264
"
The MySQL Reference Manual classes TRUNCATE in Section 13.2 "Data
Manipulation Statements"
http://dev.mysql.com/doc/refman/5.1/en/data-manipulation.html
and says it "is equivalent to a DELETE [i.e. a DML] statement".
On the other hand, Section 13.2.9. TRUNCATE Syntax says that
for non-InnoDB tables the TRUNCATE implies "dropping and
re-creating the table", which is a DDL operation:
http://dev.mysql.com/doc/refman/5.1/en/truncate.html
And Section 14.4.3.3. Compressed Table Characteristics
http://dev.mysql.com/doc/refman/5.1/en/compressed-format.html
uses TRUNCATE for an example of a DDL statement.
Oracle says TRUNCATE is DDL. So does Informix.
And actually, Oracle does have a DDL trigger for TRUNCATE.
From emails in dev-private threads "Re: DDL Triggers (2418)"
and "Re: Updated (by MarkLeith): DDL Triggers (2418)", Peter sees
that Sergei Golubchik and Guilhem Bichot and Konstantin Osipov
agree: TRUNCATE is DDL. They didn't say that it should cause
DDL trigger activation, though. Lars Thalmann has a list saying that
TRUNCATE is DML. Jim Starkey suggests that TRUNCATE should
cause activation of DELETE triggers.
Conclusion: there is no consensus that CREATE TRIGGER ...
BEFORE|AFTER TRUNCATE ... should be a DDL trigger statement.
Metadata
--------
SHOW TRIGGERS and SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
should be no problem. The EVENT_MANIPULATION column may now
contain ALTER|COMMENT|CREATE|DROP|GRANT|RENAME|REVOKE.
(It won't have "DDL" since that's just a shorthand.)
The ACTION_ORIENTATION column is 'STATEMENT' instead of
'ROW'. The EVENT_OBJECT_TABLE column may
contain something other than a table, it may contain any
object, or even '*'.
Why "ON "
----------------------
The "ON " clause allows us to create triggers
for objects within schemas. This is more than Oracle does.
Oracle allows you to have a trigger for DROP, not a trigger
for DROP of a particular table.
It might be smarter to require "ON object-type object-name".
For example, "CREATE TRIGGER t AFTER DROP ON EVENT e ..."
or "CREATE TRIGGER t AFTER DROP EVENT ON db1.* ...".
So "ON " may someday disappear from this specification.
Events
------
These two imaginary statements would do the same thing:
CREATE TRIGGER t AFTER DROP ON *.* INSERT INTO db1.t VALUES (5);
CREATE EVENT e ON DROP [DO] INSERT INTO db1.t VALUES (5);
But the methods would be different. A trigger action occurs
during the DDL statement. An event action occurs asynchronously
(or at least autonomously), on a different connection.
Advantages of CREATE TRIGGER AFTER DROP:
* it's more like Oracle
* it's faster
* you can specify whether it's BEFORE or AFTER
* if the DROP fails, the AFTER trigger won't happen
* if the trigger action fails, the DROP fails.
Advantage of CREATE EVENT ON DROP:
* Since the event action is independent of the DROP, it
doesn't affect the DROP execution, and conversely the
DROP execution, or failure therefore, doesn't affect the
event action. So for example events are better if we want
to monitor attempted DROPs by unauthorized users.
At time of writing, we don't expect to enhance CREATE EVENT
so it would work with DDL events. However, some event features
might be useful. For example, the implementor might decide
that the mysql.event table is a good place to store DDL triggers.
Example
-------
CREATE TRIGGER t1_adrop AFTER DROP ON t1_adrop SET @x = 1;
DROP TRIGGER t1_adrop;
The result should be that @x becomes 1. This is true
even though the trigger being activated is the same
trigger that we're dropping. (Peter suggests this feature
to make the task harder.) (Yes the CREATE TRIGGER is
legal because there might be a table named t1_adrop.)
Non-DDL Triggers (Or Events) (Or Scripts)
-----------------------------------------
This task is only about DDL Triggers, but people naturally
wonder where the task is for non-DDL triggers, e.g. CONNECT.
WL#1034 "Internal CRON support" said there should be a
CREATE EVENT event_name ON x DO sql_statement;
where x is SCHEDULE, DBDISKSPACE, CONNECT, RAISERROR, etc.
It didn't happen. WL#1034 is now closed.
See also dev-bugs thread ""startup" EVENTS (wl #1034)" starting with
[mysql intranet] /secure/mailarchive/mail.php?folder=104&mail=77123
Oracle allows triggers for startup/shutdown, logon/logoff, role change,
server error, or suspend. DB2 allows events for actions
on specifiable object types (databases tablespaces tables etc.).
Firebird also allows triggers on commit/rollback.
Often the required action is to write something to a log file,
and this is troublesome since we don't have an SQL "print" statement.
Often the required action is to invoke an external-language function,
and this is troublesome since we might not have initialized the DBMS yet.
WL#1220 "ON user login script" is about the initial SQL
statements that can happen when a connection occurs.
WL#2878 is about auditing connect, disconnect, etc.
BUG#17632 "Automatically trigger stored precedures
based on events (user login, shutdown)"
BUG#56176 Commit/Rollback triggers (like Firebird)
DB2 CREATE EVENT MONITOR:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0000915.html
Oracle triggers for CONNECT, TRUNCATE, etc.: See "References".
Oracle CREATE SPFILE:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6016.htm#i2072626
SQL Server 2008 Logon Triggers:
http://technet.microsoft.com/en-us/library/bb326598.aspx
References
----------
"Implementing DDL Triggers" for SQL Server 2008
http://msdn2.microsoft.com/en-us/library/ms191438.aspx
"CREATE TRIGGER" (Oracle11g manual)
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm#LNPLS01374