What are triggers in MySQL

In this article, we will discuss triggers in a MySQL database.

Table of Contents:-

• Introduction to MySQL triggers
• Where are triggers used
• Types of triggers
• MySQL create trigger example
• MySQL drop trigger example
• MySQL show triggers for a table

Introduction to MySQL triggers

As the name suggests, a trigger is a database object that is a query linked with a table that triggers or runs automatically when a particular event occurs on the table. A specific name is provided to each trigger like other database objects. We can define triggers to run when row(s) are inserted, updated, deleted in the associated table.

The row operations or conditions that turn on a trigger are known as trigger events and can activate triggers before or after their occurrence.

A few examples of triggers are to insert data to another table on insert of a row or perform a particular calculation on a column on insert of a row.

Privilege: Trigger_priv is required to enable trigger operations. Also, one must have the same privilege to create, drop, execute or show triggers for that table.

Where are triggers used?

Below are some of the primary usages of triggers:

• Many applications use triggers to impose some business rules.
• Many applications use triggers to confirm or validate input data.
• Many applications use triggers for audit purposes.
• Triggers are also used to replicate data to other sources like files for data consistency.
• Triggers sometimes are used to perform a particular action based on system functions.

Types of triggers

There are six types of triggers in MySQL which run based on two action time modifiers – BEFORE and AFTER an event has occurred. These events are INSERT, UPDATE and DELETE.

1. BEFORE INSERT TRIGGER: This trigger activates before an insert statement executes, as we can find out from the name. GO THROUGH A PRACTICAL EXAMPLE: MySQL trigger example before insert
2. AFTER INSERT TRIGGER: This trigger activates after an insert statement executes, as we can find out from the name. GO THROUGH A PRACTICAL EXAMPLE: MySQL trigger example after insert
3. BEFORE UPDATE TRIGGER: This trigger activates before an update statement executes, as we can find out from the name. GO THROUGH A PRACTICAL EXAMPLE: MySQL trigger example before update
4. AFTER UPDATE TRIGGER: This trigger activates after an update statement executes, as we can find out from the name. GO THROUGH A PRACTICAL EXAMPLE: MySQL trigger example after update
5. BEFORE DELETE TRIGGER: This trigger activates before a delete statement executes, as we can find out from the name. GO THROUGH A PRACTICAL EXAMPLE: Create trigger on delete
6. AFTER DELETE TRIGGER: This trigger activates after a delete statement executes, as we can find out from the name. GO THROUGH A PRACTICAL EXAMPLE: Create trigger on delete

MySQL create trigger example

In this section, we will see how to create triggers. We will be creating AFTER INSERT TRIGGER, but before moving ahead, let us see the create trigger syntax followed by making the sample data.

Syntax:-

CREATE TRIGGER name_of_your_trigger 
{BEFORE | AFTER} {INSERT | UPDATE| DELETE } 
ON name_of_your_table FOR EACH ROW 
trigger_code;

• name_of_your_trigger is the name given to your trigger.
• BEFORE and AFTER are the action time modifier that is trigger will activate either after or before the event.
• INSERT, UPDATE and DELETE are the events.
• name_of_your_table is the table linked with the trigger.
• trigger_code is the code that will be applied when the trigger executes.
• In MySQL Triggers, OLD and NEW keywords are used within the trigger body to distinguish between the columns Before and After the DML execution.

For sample data, create two tables, sale_person_designation, and sale_senior_managers, and insert few rows to sale_person_designation. Execute the below queries for the same.

# create the table sale_person_designation
CREATE TABLE sale_person_designation (
    id INT ,
    sale_person_name VARCHAR(255),
    designation VARCHAR(255)
);
# insert rows to sale_person_designation
INSERT INTO sale_person_designation (id,sale_person_name,designation) 
 VALUES
  (1,"Henry","Sales Executive"),
  (2,"Richa","Sales Assistant"),
  (3,"Ved","Sales Assistant");
# create the table sale_senior_managers
CREATE TABLE sale_senior_managers (
    id INT ,
    sale_person_name VARCHAR(255)
);
SELECT * FROM sale_person_designation;

Output:-

Example: Create a trigger that will insert a row to table sale_senior_managers only if a new row is inserted to the table sale_person_designation with designation = ‘Senior Sales Manager’

Observe and execute the below create trigger statement for the solution.

delimiter //
CREATE  TRIGGER insert_seniorManager_after_insert
            AFTER INSERT
            ON sale_person_designation FOR EACH ROW
            BEGIN  
              IF (NEW.designation = 'Senior Sales Manager')
              THEN
              INSERT INTO sale_senior_managers ( id, sale_person_name ) VALUES ( NEW.id, NEW.sale_person_name);
     END IF;
 END; // 

Action Output:-

Schema Tab on MySQL Workbench:-

Let us now try and insert two rows to the sale_person_designation table and verify if the trigger is working.

INSERT INTO sale_person_designation (id, sale_person_name, designation) 
 VALUES(4,"Rachel","Senior Sales Manager");
INSERT INTO sale_person_designation (id, sale_person_name, designation) 
 VALUES(5,"Veronica","Sales Executive");

Action Output:-

To view what got inserted into both tables.

SELECT * FROM sale_person_designation;

Output:-

SELECT * FROM sale_senior_managers;

Output:-

The output in image_6 shows that only one salesperson got inserted to the sale_senior_managers table whose designation is Senior Sales Manager.

MySQL drop trigger example

In this section, we will see how to drop triggers.

Syntax:-

 DROP TRIGGER [IF EXISTS] name_of_your_trigger;

Example: Drop the trigger insert_seniorManager_after_insert.

DROP TRIGGER IF EXISTS insert_seniorManager_after_insert;

Output:-

MySQL show triggers for a table

In this section, we will see how to retrieve the name of triggers associated with a table.

Syntax:-

SHOW TRIGGERS [{FROM | IN} name_of_your_database] [LIKE pattern_definition | WHERE expression]

Here, the LIKE clause finds the match respective to the table and not the trigger.

Example: Show the triggers linked to the table sale_person_designation.

SHOW TRIGGERS LIKE 'sale_person_designation' ;

Output:-

The output in image_8 shows that there is only one trigger is associated with the table sale_person_designation.

READ MORE:

• Create trigger on delete
• Trigger with if condition in mysql
• MySQL trigger example after update
• MySQL trigger example before update
• MySQL trigger example after insert
• MySQL trigger example before insert

We hope this article helped you with the building blocks of triggers in MySQL. Good Luck!!!