Sheeri talked a little about MySQL Triggers in One TRIGGER Fact, Optimizer Rewriting Stuff. While it’s great that MySQL 5.0 has Triggers, there are some things that I feel could become features in future releases.
IF EXISTS
One of the beautiful features that MySQL has is IF EXISTS. This ternary operation that if the object exists performs the operation, of not it does nothing works wonders in reviewing logs for errors. One of the problems with Oracle for example, is the requirement to ignore the ORA errors for non-existent objects.
But this functionality doesn’t exist for Triggers? One must wonder why. I’d like to see this.
mysql> DROP TRIGGER IF EXISTS trigger_name;
REPLACE
On feature that simplifies the lack of IF EXISTS functionality using Oracle is REPLACE. The syntax is:
oracle> CREATE OR REPLACE TRIGGER trigger_name...
In this case, this functionality effectively eliminates the need for a DROP IF EXISTS, however I’d not like to see this introduced into MySQL especially it’s an Oracle specific syntax and not ANSI SQL.
Multiple Triggers of same type per table
MySQL only allows one Trigger per type per table. That is, there can only be one BEFORE UPDATE for example. While you may ask the question why you would need this functionality. Here is a typical situation.
You use Triggers to perform some level of business functionality, determining values for optimised (denormalised) columns is a good example. So you need to write an appropiate trigger for that piece of functionality.
You also use Triggers to perform database auditing, that is for every insert/update/delete of data, you record a full copy of the change in an audit database. One way to ensure this is consistent across the entire database is to implement via triggers. So you leverage programming functionality to pre-create triggers for all tables to manage this.
The problem with MySQL occurs in that you have to now merge these triggers for tables that require both. If you want to deploy your application into a test environment, you may wish to not deploy your auditing triggers, but now you have this functionality mixed in with business logic.
Multi Type Triggers
Another cool Oracle feature is the capacity to define a trigger for multiple types. For example:
oracle> CREATE OR REPLACE TRIGGER trigger_name oracle> BEFORE INSERT OR DELETE OR UPDATE ON table_name ...
In this example, with MySQL you would need to create three seperate triggers.
Other features
While not as important and one would need to consider if necessary in MySQL are some other Oracle provided trigger functionality. This includes:
- WHEN CLAUSE trigger restriction
- Triggers on DDL Statements such as CREATE, DROP, ALTER
- Triggers on Database events such as LOGON, LOGOFF, STARTUP, and SHUTDOWN
- INSTEAD OF Triggers (for Views)
- STATEMENT based trigger
For more information you can check out the Oracle Documentation on Introduction to Triggers, CREATE TRIGGER, Documentation Search on Triggers
The thing I dislike the most about MySQL’s triggers is that they are row at a time. Writing a trigger in MySQL is like writing a loop over a result set. The trigger fires once per row. Compare this to Microsoft SQL Server’s triggers, where the inserted, updated or deleted data is available to the trigger as a set, and you can (and should) perform a bulk operation on the entire set. Instead of firing once per row, it fires once per statement.
It’s important to have all the data available in one statement. For example, I might only want to do something if the entire statement had some characteristic. Imagine a trigger to detect fraudulent orders (naive example, I know): raise an alert if the sum of the item prices on the order is more than a certain amount.
I think this is much more in line with SQL’s way of doing things, too.
I’m not sure if this is what you mean by STATEMENT based trigger. This would be my highest priority wish for triggers.
I agree about the awkwardness of requiring only one trigger per type per table.
If you allow multiple triggers per type, it is also useful to offer a way to declare the order in which they execute. So one trigger adjusts input values to be more uniform, and a subsequent trigger does some logging. You’d definitely want the order of these triggers to be dependable.
Another feature MySQL triggers lack is some way to raise an exception, to abort the operation that spawned the trigger. The only way I have found to do this is to set a not-null column to NULL in a BEFORE trigger. This aborts the calling operation, but the error message is always about violating the not-null constraint. It would be better to allow user-definable exceptions with an associated user-definable error message.
InterBase/Firebird offers a good solution to both of the above issues. MySQL should “borrow” the concepts and implement them.
Much agreed on all of your, Baron, and Bill’s comments and suggestions. As an FYI, many of these suggestions are currently on the Worklog in various forms. For instance:
WL#3253 “FKEYS & Triggers: implement support for multiple triggers per table”
Summary:
Implement support for multiple triggers of the same type (same action and
timing) for the table. Triggers of the same action and timing should be
executed in the order of creation.
There will be a new column INFORMATION_SCHEMA.TRIGGERS.CREATED,
DATETIME, nullable. It will contain the date and time of creation.
Compare INFORMATION_SCHEMA.ROUTINES.CREATED. There will be no
INFORMATION_SCHEMA.TRIGGERS.LAST_ALTERED column. A trigger’s
CREATED time will be the time of the CREATE TRIGGER statement’s
execution. It will be NULL for some older triggers, and NULL is
“less than” any other datetime for purposes of determining the
order of creation. There will be a new “CREATED=” clause like
the “DEFINER=” clause, as a possible aid for replication. The
new column will not appear in SHOW TRIGGERS. If an attempt is
made to define two triggers with the same action + the same
timing + the same CREATED time, there will be an error return.
If a trigger makes changes, then a trigger which comes later
will see the changed values in any NEW transition variables.
WL#3129 “Consistent Clauses for CREATE and DROP”
Summary:
There are clauses or options that apply to
some CREATE and DROP statements, but not all.
We could be more consistent.
The inconsistency
—————–
We’re talking about IF EXISTS / IF NOT EXISTS
in CREATE and DROP, and about the number of
objects that we can drop in one statement.
Here, have a chart:
Object CREATE IF NOT EXISTS DROP IF EXISTS DROP MANY
—— ——————– ————– ———
DATABASE yes yes –
EVENT yes yes –
FUNCTION – yes –
INDEX – – –
PROCEDURE – yes –
TABLE yes yes yes
TRIGGER – – –
USER – – yes
VIEW – yes yes
A “yes” in the “CREATE IF NOT EXISTS” column means
CREATE object_type object_name IF NOT EXISTS is legal, e.g.
“CREATE DATABASE d IF NOT EXISTS …”.
A “yes” in the “DROP IF EXISTS” column means
DROP object_type object_name IF EXISTS is legal, e.g.
“DROP EVENT IF EXISTS e”
A “yes” in the “DROP MANY” column means
DROP object_type object_name [, object_name …] is legal, e.g.
“DROP TABLE t1,t2,t3″
WL#2110 “Stored Procedures: Implement SIGNAL”
Summary:
MySQL will implement the standard SQL (SQL:2003) SIGNAL
statement, which is used to signal a rule violation
(i.e. an error or a warning) that happens when a stored
procedure is executed.
This is a subtask needed for stored procedures. It was
decided at the Malta conference that this feature does
not need to be implemented for the MySQL 5.0 release.
It is now due for 5.1.
SIGNAL depends upon the implemention of the RESIGNAL
statement.
Rationale
———
— Needed functionality:
Standard SQL requires that, for stored procedures, a
SIGNAL statement be supported, to allow an exception
condition (error or warning) to be signaled to the user
when a stored procedure is executed, and to clear the
diagnostics area. MySQL 5.0 currently does not support
this functionality.
Hope this helps somewhat!
Jay