In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table.

It can be performed when you run one of the following MySQL like INSERT, UPDATE and DELETE occurred in a table. Its activation time can be BEFORE or AFTER.

Types of Triggers in MySQL

  • Before Insert: This is done before inserting data into the table.
  • After Insert: This is after data has been entered into the table.
  • Before update: This is activated prior to the updating of the data in the table.
  • After update: This function is activated upon the updating of the table's data.
  • Before delete: This is done before any data are removed from the table.
  • After delete: This function is activated when the data has been deleted from the table.

What is the purpose of triggers in MySQL?

  • It is easy to set up triggers.
  • Triggers allow us to validate data before it is inserted or updated.
  • Triggers allow us to keep track of records, such as audit trails for tables.
  • Triggers increase the performance of SQL queries as it doesn't need to compile every time the query executes.
  • Triggers can be used to reduce client-side code, which saves time and effort.
  • It allows us to scale our applications across multiple platforms.
  • SQL triggers are an alternative method to verify the integrity of data.
  • Triggers are an alternative method to perform the task.

Syntax

CREATE TRIGGER trigger_name    
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON table_name FOR EACH ROW    
         BEGIN
        --trigger code    // You can write here your code
        END;

BY Best Interview Question ON 20 Sep 2022

Example

delimiter //
CREATE TRIGGER age_check BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;
delimiter ;