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
