Author

Triggers in MySQL

Image

by Samarth Sinha on 2017-05-15 07:00:35

A SQL trigger 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.

A SQL trigger 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.

Advantages of using SQL triggers

  • SQL triggers provide an alternative way to check the integrity of data.
  • SQL triggers can catch errors in business logic in the database layer.
  • SQL triggers provide an alternative way to run scheduled tasks. By using SQL 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 the tables.
  • SQL triggers are very useful to audit the changes of data in tables.

Disadvantages of using SQL triggers

  • SQL 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.
  • SQL triggers are invoked and executed invisible from the client applications, therefore, it is difficult to figure out what happen in the database layer.
  • SQL triggers may increase the overhead of the database server.

Creating MySQL trigger

In order to create a new trigger, you use the CREATE TRIGGER statement:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
...
END;


About the Author

  • avatar
    Samarth Sinha

    My name Samarth Sinha. I work on developing Web Applications using PHP and MySQL and in different frameworks of PHP like Laravel, CakePHP, CodeIgnitor and Smarty. In databases I have worked in MySQL and Postgres. I started this blog to share my knowledge to those who wants to start developing Web Applications using PHP.