MySQL – Triggers

In MySQL, a trigger is a set of SQL statements that are stored in a system catalog. This is a type of stored procedure that is invoked automatically in response to an event. An individual trigger is associated with a table, which is activated by any DML statement, including INSERT, UPDATE, and DELETE.

Triggers are referred to as special procedures because they cannot be called directly like stored procedures. The primary difference between a trigger and a procedure is that a trigger is called automatically when a data modification event occurs against a table. A stored procedure, on the other hand, must be called explicitly.

The SQL standard defines two types of triggers: row-level triggers and statement-level triggers.

Row-Level Trigger:

Triggers are activated by triggering statements such as insert, update, or delete for each row. When multiple rows are inserted, updated, or deleted in a table, the row trigger is automatically invoked for every row affected.

Statement-Level Trigger:

An event trigger fires once for each occurrence of an event on a table, regardless of the number of rows inserted, updated, or deleted.

• In order to enforce business rules, triggers are used.
• We use triggers to validate data even before it is inserted or updated.
• As a result of triggers, we are able to keep track of records in the database, such as audit trails in tables.
• The use of SQL triggers provides an alternative method of checking the integrity of data.
• A trigger provides an alternative method of executing a scheduled task.
• The use of triggers increases the performance of SQL queries since they avoid the need for the query to be compiled each time it is executed.
• Using triggers reduces client-side code, which saves time and effort for developers.
• We use triggers to scale our application across a variety of platforms.
• The triggers are easy to maintain.

Limitations of Using Triggers in MySQL

• It is not possible to use all validations with MySQL triggers; they only provide extended validations. The NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints can be used to validate simple data.
• Triggers are invoked and executed invisibly by the client application. Therefore, it is difficult to troubleshoot problems at the database layer.
• The overhead of the database server may be increased by triggers.

Types of Triggers 

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete