MySQL Triggers

MySQL trigger is a function of the stored procedure to respond to the system program. This function responds and runs any data table event automatically. You can use it for the insert, update, and delete procedure of the table.

The trigger procedure works when table data modifies and changes as per requirement. This stored procedure works in two ways: the first is a row-level trigger, and the second is a statement-level trigger.

• MySQL row-level trigger
  • This trigger works when any row of the table changes or modifies its data.
  • The row-level trigger works on a small data operation of each row of the table.
  • This trigger works automatically on the insert, update, and delete events of the single table row.  
• MySQL statement-level trigger
  • This trigger works automatically when an entire row of the table modifies its data.
  • This statement-level trigger works on the insert, update, and delete events of the multiple table row. 
  • This trigger comes with all data operations with the MySQL statement.

Syntax of the MySQL triggers

The MySQL trigger procedure used multiple events on the table data. The activation time of the trigger is based on the operations, and the naming convocations of the trigger use to set the MySQL trigger procedure.

The syntax of the MySQL trigger shows below:

[before|after] table-name [insert | delete| update]

• The before and after is the activation time of the trigger. You can choose the activation time of the trigger procedure.
• MySQL events are inserted, deleted, and updated used to set the operation of the table data.
• The table name is represented in the MySQL table where the trigger procedure is applied.

Type of the MySQL triggers

The MySQL triggers procedure can create several triggers because of the events and time activation. The type of MySQL triggers shows below:

• BEFORE INSERT: This trigger activates before inserts information in the given table.
• BEFORE DELETE: This trigger automatically activates before deletes information from the given table.
• BEFORE UPDATE: This trigger works before data updates in the given table.
• AFTER INSERT: This trigger automatically works on the inserts data in the table.
• AFTER DELETE: This trigger activates after deleting the data from the table.
• AFTER UPDATE: This trigger type activates after modifies the information of the table.

How does the MySQL trigger work?

The MySQL triggers procedure based on the events and time activation. There are several operations of the MySQL triggers, and the working procedure of the trigger shows below:

• Create trigger: Create a mysql trigger using the trigger name on the required table.

CREATE TRIGGER trigger_names ON table_names FOR EACH ROW

BEGIN

-- Trigger query

END;

• Use MySQL trigger type: you can use the types of the MySQL trigger.  This trigger uses time and event.

[before | after] [insert | delete | update]

• Show trigger: Display available triggers in the MySQL system.

SHOW triggers;

• Delete trigger: Delete or remove the available triggers of the MySQL system.

DROP TRIGGER database_name.trigger_name;

How to create MySQL triggers

The following syntax shows the creation of the MySQL triggers:

CREATE TRIGGER trigger_names 
      [before | after] [insert | delete | update]
ON table_names FOR EACH ROW
BEGIN
-- Declaration of the variable
-- Trigger query
END;

   Description

• The MySQL query uses create keyword with trigger name to create a new trigger procedure.
• The trigger procedure uses one trigger based on the time activation and the events on the table.
• The procedure requires a table and sets either a row-level trigger (EACH ROW) or a statement-level trigger.
• This code starts with the BEGIN keyword and finishes with the END keyword.
• You can use and operate trigger code in between the begin and end keywords.

Use of the MySQL triggers

There are some reasons and functions to use the MySQL trigger. This trigger needs in the MySQL system because of the following features.

• MySQL triggers can maintain easily in the system.
• MySQL trigger reduces code and saves the effort and time of the database developer.
• MySQL triggers work on the different platforms of the system.
• This function provides an alternative method to check data integrity.
• The trigger keeps a record of the data and maintains it.
• It implements business rules to operate the database of the application.
• The trigger increases the performance of the SQL query and does not need to be compiled for execution.
• The trigger helps to schedule the task of the execution process.
• The trigger works procedure and events before and after time activation.

Limitation of the MySQL triggers

• The trigger increases the database server because of event and time activation.
• The triggers execute invisibly in the MySQL database application. You can see the operation of the data.
• You cannot troubleshoot the problem of the database information and execution process.
• The trigger does not support MySQL constraints and validation. You can use simple and basic constraints like UNIQUE, CHECK, and so on. 

Operation of the MySQL triggers

Create MySQL trigger

MySQL system creates a new trigger for operation. The trigger creates on the existing table. If the table contains data or information, then the trigger shows the working procedure. The "CREATE TRIGGER" statement requires a new trigger. You can suggest a unique trigger name for a table.

Syntax

CREATE TRIGGER trigger_names trigger_times trigger_events
ON table_names FOR EACH ROW
BEGIN
-- Declaration of the variable
-- Trigger query
END;

• The trigger_names uses to create specific triggers of the database.
• The table_name uses to select the particular table of the database.
• The system uses the "CREATE TRIGGER" statement for creating an unwanted trigger in the system.
• The trigger time used to activate before and after the operation of the data.
• The trigger event uses to modify the database using events. The events apply to trigger such as insert, delete, update events.
• The BEGIN and END block to write code related to trigger. You can declare the variable inside of the trigger block.

Drop MySQL trigger

MySQL system can delete the existing triggers of the database table. The "DROP TRIGGER" statement requires deleting the trigger.

Delete available trigger syntax show below:

DROP TRIGGER trigger_names;

Delete existing trigger in database syntax shown below:

DROP TRIGGER if exists database_name.trigger_names;

• The trigger_names is used to delete specific triggers of the database.
• The database_name is used to select a particular database and table.
• The "if exists" statement is used to notify whether a trigger is available or not.
• The system uses the "DROP TRIGGER" statement for removing unwanted triggers in the system.

Show MySQL trigger

MySQL system needs to show available triggers of the database. The "SHOW TRIGGERS" statement requires displaying triggers, and you can get available triggers from the database.

Show MySQL trigger syntax show below:

Show triggers;

• The system uses the "SHOW TRIGGERS" statement for displaying available triggers in the system.
• You can get all information related to the triggers.

This operation helps to use trigger types in the database. You can modify data as per requirement.