MySQL finally supports one of the most important features of an enterprise database server which is called trigger since version 5.0.2. Trigger is implemented in MySQL by following the syntax of standard SQL:2003. When you create a trigger in MySQL, its definition stores in the file with extension .TRG in a database folder with specific name as follows:
/data_folder/database_name/table_name.trg
The file is in plain text format so you can use any plain text editor to modify it.
While trigger is implemented in MySQL has all features in standard SQL but there are some restrictions you should be aware of like following:
- It is not allowed to call a stored procedure in a trigger.
- It is not allowed to create a trigger for views or temporary table.
- It is not allowed to use transaction in a trigger.
- Return statement is disallowed in a trigger.
- Creating a trigger for a database table causes the query cache invalidated.
Query cache allows you to store the result of query and corresponding select statement. In the next time, when the same select statement comes to the database server, the database server will use the result which stored in the memory instead of parsing and executing the query again.
- All trigger for a database table must have unique name. It is allowed that triggers for different tables having the same name but it is recommended that trigger should have unique name in a specific database. To create the trigger, you can use the following naming convention: (BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)