Triggers in MySQL

A trigger is a stored program that gets invoked automatically whenever any event such as insertion, update, or deletion occurs in a table.

For instance, whenever we add a new row or delete a row, we can define a trigger that gets triggered automatically through which we can be assured of the proper happenings of those events. Each trigger is associated with a table.

The trigger is a special procedure but not a stored procedure. The key difference between both is that a stored procedure needs to be called explicitly while the special procedure(trigger) gets called automatically when the event linked to it happens.

Use of triggers:

  • Data can be validated even before they are inserted or updated with the help of triggers.
  • A log of records can be kept using triggers.
  • Triggers provide an alternative way to check the integrity of data.
  • Triggers also provide an alternative way to run the scheduled task.
  • The performance of SQL queries can be improved with triggers because it does not need to compile each time the query is executed.

Limitations of triggers:

  • There will be an increase in the overhead of the database server with the usage of triggers.
  • Users can not troubleshoot what happens in the database layer as they get invoked automatically and invisibly.

How to create triggers:


Step1: Creating a table named person

Step2. Code for trigger creation

Step3: Inserting values into the table named person


Here, we are changing the delimiter to // so that it will not be terminated for the default termination character(;). With the help of this trigger, conditions like entering negative

values can be checked and acted upon by replacing them with values like 0 or any other value.


Step1: First create a table named test_trigger as follows:

Step2: Creating a table named person

Step3: Create the trigger



Step4. Inserting values into a table named person


The above trigger adds the value based on what the user inserts into the gender column of the employee table. If we execute the “SELECT * FROM test_trigger” it will show the message column with the corresponding values depending on the INSERT operation on the employee table

To check all the triggers available in a database and associated with a table, the following statement works:

To drop a particular trigger, the following statement works:

In a similar fashion, triggers for conditions like before_updation, after_updation, before_deletion, and after_deletion can be created and used.

Leave a Reply