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:

Example: 

Step1: Creating a table named person

Step2. Code for trigger creation

Step3: Inserting values into the table named person

INSERT INTO person VALUES    

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.

Example2:

Step1: First create a table named test_trigger as follows:

Step2: Creating a table named person

Step3: Create the trigger

BEFORE INSERT ON employee FOR EACH ROW  

BEGIN  

Step4. Inserting values into a table named person

INSERT INTO employee VALUES    

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