What are MySQL Triggers and How to create them?

What are MySQL Triggers?

MySQL Trigger is a code that triggers certain actions that should happen when the particular operation performs on the database. It means that the trigger tells the database to execute specific commands when a specifically mentioned event happens. The mentioned event could be an insert, update, or delete command.

These MySQL triggers are extremely powerful and helpful when it comes to automating various tasks in the database.

Example:

The organization has to send the ‘Welcome Mail’, whenever the information of any new customer is added to the database. The manual task is possible when there are only 5-6 new customers every day.

When the number is large, you have to automate the task for the sake of efficiency.

There, MySQL triggers do a fantastic job.

The trigger is set to invoke an action (Welcome Mail), which is trigger by the MySQL trigger whenever the new information (new customer information) is added to the DB.

MySQL Syntax:

Well, now you understand what MySQL Trigger is, let’s read the syntax.

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 …
 END;

Trigger_name: the name of the trigger that you have to create. Put after ‘Create Trigger’ statement.

Trigger_time: the time for the activation of the trigger. BEFORE to trigger the action before the change in the table, and AFTER to process the work after the difference in the table.

Trigger_event: INSERT, UPDATE, or DELETED – they are the event that executes the triggers.

Table_name: the name of the table the trigger will work on. The action would only be performed on the table, so a table name is a necessity for the MySQL triggers.

Begin-End: The space for writing the logic of the trigger.

So, this was the Syntax for MySQL Triggers.

How to create MySQL triggers?

Step 1: Creating the table to apply the trigger upon.

mysql> Create table teacher_age(age INT, Name Varchar(35));
Query OK, 0 rows affected (0.80 sec)

The table is collecting the age of teachers.

Step 2: Creating the trigger

mysql> DELIMITER //
 mysql> Create Trigger before_inser_teacherage BEFORE INSERT ON teacher_age FOR EACH ROW
 BEGIN
 IF NEW.age < 0 THEN SET NEW.age = 0;
 END IF;
 END //
 Query OK, 0 rows affected (0.30 sec)

The trigger is to change the value of the age into 0 if anyone puts the negative value in the age table.

Step 3: Inserting the data to invoking the trigger

mysql> INSERT INTO teacher_age(age, Name) values(30, 'Achir');
 Query OK, 1 row affected (0.14 sec)

mysql> INSERT INTO teacher_age(age, Name) values(-10, 'Ajit');
 Query OK, 1 row affected (0.11 sec)

mysql> Select * from teacher_age;
 +------+---------+
 | age  | Name    |
 +------+---------+
 | 30   | Achir   |
 | 0    | Ajit    |
 +------+---------+
 2 rows in set (0.00 sec)

Here, as we see, the age of Ajit age turns to 0 when the value was -10 in the table.

Different types of MySQL Triggers

1. Before Update Trigger:

The action of the trigger will be performed prior to the change happens in the database.

2. After Update Trigger:

After the change in the database, the trigger will execute the command.

3. Before Insert Trigger:

In this case, the trigger will be active before the insertion of the data.

4. After Insert Trigger:

The trigger gets activated after an insert operation is executed.

5. Before Delete Trigger:

The trigger is invoked before a delete occurs or before the deletion statement is implemented.

6. After Delete Trigger:

The trigger is invoked after a delete occurs, or after a delete operation is implemented.

Advantages of MySQL Triggers:

• Data Integrity: It is possible to check the integrity of the data with the help of MySQl triggers.
• Catching Errors: MySQL triggers are also useful in finding the errors in business logic in the database layer.
• Scheduled Tasks: Running a scheduled task with the automated process. Hence, no delay in running the event, trigger it with ‘before’ or ‘after’ statemen make the work fast.
• Invalid Transactions: MySQL triggers are helpful in the prevention of invalid transactions.
• Event Logging: MySQL triggers can log an event and can also store the information on the access of the table.

Disadvantages of MySQL Triggers:

• Validations Replacement: MySQL triggers does not replace the validations. They can only provide extended validation.
• Client Applications: MySQL triggers are invoked and executed invisibly from client applications. That made it very difficult to figure out what happens in the database layer.
• Server Load: These triggers can impose a high load on the database server.
• High velocity of Data: Wehn there is a flow of high-velocity data, the trigger does not work efficiently. This happens because, in the case of high-velocity data, the triggers are invoked all the time.

Wrap Up

That’s all.

I hope this article helps you to understand what is MySQL triggers and how to create MySQL trigger.

If there is any doubt, let us know in the comment section.