Skip to main content

What is a Trigger

In this article, we will be discussing one of the most important concepts in MySQL: Triggers. Triggers are a very important part of MySQL and they allow us to perform certain actions based on events that occur in our database.

What is a Trigger?​

A trigger is a stored procedure in the database that automatically responds to an event such as an insert, update, or delete that occurs in a table. The trigger is then executed, or fired, when the event happens.

Triggers can be very useful for maintaining the integrity of the information in the database. They can also automate certain database operations, which can make your database more efficient and your workload lighter.

Working of Triggers​

Triggers are associated with tables in the database. They are defined to activate on INSERT, UPDATE, and DELETE commands. The triggers can be set to fire either before or after the event they are associated with.

  • A trigger that fires before an event is called a BEFORE trigger.
  • A trigger that fires after an event is called an AFTER trigger.

For example, a BEFORE INSERT trigger would fire before a new record is inserted into a table.

Types of Triggers​

In MySQL, there are six types of Triggers:

  1. BEFORE INSERT: Activated before a new row is inserted.
  2. AFTER INSERT: Activated after a new row is inserted.
  3. BEFORE UPDATE: Activated before a row is updated.
  4. AFTER UPDATE: Activated after a row is updated.
  5. BEFORE DELETE: Activated before a row is deleted.
  6. AFTER DELETE: Activated after a row is deleted.

Creating a Trigger​

The syntax to create a trigger in MySQL is as follows:

CREATE TRIGGER trigger_name 
trigger_time trigger_event 
ON table_name FOR EACH ROW 
trigger_body;

Where:

  • trigger_name: Name of the trigger.
  • trigger_time: Specifies when the trigger will be executed. It can be BEFORE or AFTER.
  • trigger_event: The event that will activate the trigger. It can be INSERT, UPDATE, or DELETE.
  • table_name: The table to which the trigger is associated.
  • trigger_body: The block of statements to be executed when the trigger is fired.

Wrapping Up​

In conclusion, triggers are an integral part of MySQL that allow us to automate tasks and maintain the integrity of the information in our database. They are defined for a specific table and are activated when certain events (such as an insert, update, or delete) occur in that table. Triggers can be set to fire before or after these events.

In the next article, we'll dive deeper into the syntax and examples of using triggers in MySQL. Stay tuned!