Table Trigger In MySQL

blog-banner

Trigger is a stored program that invoked automatically in response to an event such as Insert, Update and Delete operation with an associated table.

MYSQL Supports trigger that invoked in a response of Insert, Update or Delete Operation with associated table of database.

There are mainly two types of triggers as per SQL standard :

  1. Row-level trigger

    This type of trigger is invoked for each row which is Inserted, Updated or Deleted.

  2. Statement-level trigger

    This type of trigger is invoked once for each transaction regardless of how many rows got affected. 

MySQL Only supports Row-level trigger it doesn’t support Statement-level trigger. So Let’s see how Row-level trigger works.

There are certain benefits to use MySQL trigger which are as follows :

  1. Trigger handle error from database level. It gives alternate to run scheduled tasks before or after performing an action on associated tables.
  2. Triggers can be useful for auditing the changes in tables data.

There is one main disadvantage of using a row-level trigger, that it may create overhead of MySQL server.

Let's create one table inside the database to understand the example.

DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Now let’s see how can we create and manage Triggers :

Procedure for Creating Trigger:

  1. First, we have to specify the name of the trigger to CREATE TRIGGER clause.
  2. In the second line, we have to specify a time to invoke the trigger.
  3. In the third line, we have to specify the name of a table where the trigger is associated with it.
  4. Finally, we have to specify the trigger body which contains SQL statement which got executed when the trigger is invoked in BEGIN and END clause. NOTE: BEGIN and END clause only used when you have multiple statements in the trigger body. For a single statement, you can write a direct body part after the third step.

There are mainly six types of Triggers

  1. BEFORE INSERT TRIGGER

    This trigger is invoked before an Insert event occurs in the associated table.

    Command for creating BEFORE INSERT TRIGGER
    CREATE  TRIGGER trigger_name
    BEFORE INSERT
    ON table_name FOR EACH  ROW 
    BEGIN
    --statements
    END

    Create another table to keep the user track who insert the record in employee table

    DROP TABLE IF EXISTS audit;
    CREATE TABLE audit(
        history VARCHAR(20) NOT NULL
    );
    

    Here is the query of BEFORE INSERT trigger.

    CREATE TRIGGER before_employee_insert
    BEFORE INSERT ON employee
    FOR EACH ROW
        insert into audit(history) values(current_user());
    

    Note: In BEFORE INSERT TRIGGER you can access and change the new values. However, you cannot access old values because it doesn’t exist.

  2. AFTER INSERT TRIGGER

    This trigger is invoked after an Insert event occurs in the associated table.

    Command for creating AFTER INSERT TRIGGER

    CREATE  TRIGGER trigger_name
    AFTER INSERT
    ON table_name FOR EACH  ROW
    BEGIN
    --statements
    END
    

    Note: In AFTER INSERT TRIGGER you can access and change the new values. However, you cannot access old values because it doesn’t exist.

  3. BEFORE UPDATE TRIGGER

    This trigger is invoked before an update event occurs in the associated table.

    Command for creating BEFORE UPDATE TRIGGER

    CREATE  TRIGGER trigger_name
    BEFORE UPDATE
    ON table_name FOR EACH  ROW
    BEGIN
    --statements
    END
    

    Note: In BEFORE UPDATE TRIGGER you can access the new value and the old value both.

  4. AFTER UPDATE TRIGGER

    This trigger is invoked after an update event occurs in the associated table.

    Command for creating AFTER UPDATE TRIGGER

    CREATE  TRIGGER trigger_name
    BEFORE UPDATE
    ON table_name FOR EACH  ROW
    BEGIN
    --statements
    END
    

    Note: In BEFORE UPDATE TRIGGER you can access the new value and the old value both.

  5. BEFORE DELETE TRIGGER

    This trigger is invoked before a delete event occurs in the associated table.

    Command for creating BEFORE DELETE TRIGGER

    CREATE  TRIGGER trigger_name
    BEFORE DELETE
    ON table_name FOR EACH  ROW
    BEGIN
    --statements
    END
    

    In BEFORE DELETE TRIGGER you can access the old value.

  6. AFTER DELETE TRIGGER

    This trigger is invoked after a delete event occurs in the associated table.

    Command for creating AFTER DELETE TRIGGER

    CREATE  TRIGGER trigger_name
    AFTER DELETE
    ON table_name FOR EACH  ROW
    BEGIN
    --statements
    END
    

    Note: In AFTER DELETE TRIGGER you can access the old value.

Check Trigger

To check the trigger list we can use the below command

SHOW TRIGGERS
FROM | IN database_name
Where Search_condition

As per the above command, we can get a list of the trigger but If we just write SHOW TRIGGERS keyword it will return all the triggers (You need to select database before that else it will throw an error).

Single database should be used with FROM keyword and to use multiple database we should use IN keyword

We can use any condition depends on our needs. It could be like

Where table name = “table_name”

DROP TRIGGER

To Drop Trigger from database we can use below command :

We can use any condition depends on our needs. It could be like

DROP TRIGGER [IF EXISTS] [schema_name].trigger_name;

First, we have to specify the name of the trigger that we want to drop after DROP TRIGGER keywords.

Secondly, we specify the name of the schema for which the trigger belongs to otherwise it will drop the trigger of the current database.

Third, we can specify IF EXISTS keyword optionally to drop the trigger. But if you missed to specify IF EXISTS keyword and database itself do not have any trigger with the specified name then the database will throw an error.

Contact us

For Your Business Requirements

Text to Identify Refresh CAPTCHA
Background Image Close Button

2 - 4 October 2024

Hall: 10, Booth: #B8 Brussels, Belgium