If knowledge is power, then sharing knowledge is ultimate powertrip
Latest Updates
You are here: Home >> MySql >> Triggers in MySql

Triggers in MySql

Triggers in mysql  is a set of Sql statements stored in the database catalog. A Sql trigger is executed or fired whenever an event associated with a table occurs e.g.,  insert, update or delete.

Triggers in mysql is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

It is important to understand MySql trigger’s advantages and disadvantages so that you can use it appropriately. In the following sections, we will discuss about the advantages and disadvantages of using MySql triggers.

Advantages of using Triggers in Mysql

  • MySql triggers provide an alternative way to check the integrity of data.
  • MySql triggers can catch errors in business logic in the database layer.
  • MySql triggers provide an alternative way to run scheduled tasks. By using MySql triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked  automatically before or after a change  is made to the data in tables.
  • MySql triggers are very useful to audit the changes of data in tables.

Disadvantages of using Triggers in MySql

  • MySql triggers only can provide an extended validation and they cannot replace all the validations. Some simple validations have to be done in the application layer. For example, you can validate user’s inputs in the client side by using JavaScript or in the server side using server side scripting languages such as JSP, PHP, ASP.NET, Perl, etc.
  • MySql triggers are invoked and executed invisibly from client-applications therefore it is difficult to figure out what happen in the database layer.
  • MySql triggers may increase the overhead of the database server.

In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table. A trigger can be defined to be invoked either before or after the data ischanged by INSERT, UPDATE or DELETE statements. MySQL allows you to define maximum six triggers for each table.

  • BEFORE INSERT – activated before data is inserted into the table.
  • AFTER INSERT– activated after data is inserted into the table.
  • BEFORE UPDATE – activated before data in the table is updated.
  • AFTER UPDATE – activated after data in the table is updated.
  • BEFORE DELETE – activated before data is removed from the table.
  • AFTER DELETE – activated after data is removed from the table.

When you use a statement that makes change to the table but does not use INSERTDELETE or UPDATE statement, the trigger is not invoked. For example, the TRUNCATE statement removes the whole data of a table but does not invoke the trigger associated with that table.

There are some statements that use the INSERT statement behind the scenes such as REPLACE statement and LOAD DATA statement. If you use these statements, the corresponding triggers associated with the tables if available will be invoked.

Triggers defined for a table must have a unique name. You can have the same trigger name that defines for different tables but it is not recommended. In practice, the names of triggers follow the following naming convention:

1
(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)

 

Storage of Triggers in Mysql

MySQL stores triggers in a data directory e.g., /data/classicmodels/ with the files namedtablename.TRG and triggername.TRN:

  • The tablename.TRG file maps the trigger to the corresponding table.
  • the triggername.TRN file contains the trigger definition.

You can back up the MySQL triggers by copying the trigger files to the backup folder. You can also backup the triggers using the mysqldump tool.

MySQL Trigger Limitations

MySQL triggers have all features in standard SQL however there are some limitations that you should know before using them in your applications.

MySQL triggers cannot:

  • Use SHOWLOAD DATALOAD TABLEBACKUP DATABASE, RESTOREFLUSH and RETURNstatements.
  • Use statements that commit or rollback implicitly or explicitly such as COMMITROLLBACKSTART TRANSACTIONLOCK/UNLOCK TABLESALTERCREATEDROPRENAME, etc.
  • Use prepared statements such as PREPAREEXECUTE, etc.
  • Use dynamic SQL statements.
  • Call a stored procedure or stored function.

About Jawed Shamshedi

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Scroll To Top