WL#2418: DDL Triggers

Affects: Server-7.1   —   Status: Un-Assigned   —   Priority: Medium

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 <trigger name>   
{ BEFORE | AFTER }   
{ALTER|COMMENT|CREATE|DROP|GRANT|RENAME|REVOKE | DDL}   
ON <object name>   
[ FOR EACH STATEMENT ]             /* does nothing. we might skip this. */   
<triggered SQL statement>   

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 <object name>" 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 <object-name>" 
---------------------- 
 
The "ON <object-name>" 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 <object-name>" 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