Creating and using MySQL triggers with examples of before and after triggers
Here we are going to learn how to create and use the MySQL triggers before, and after inserting, deleting, & update triggers
MySQL BEFORE INSERT Trigger
To maintain a summary table of another table, you will learn how to create a MySQL BEFORE INSERT trigger here.
BEFORE INSERT triggers in MySQL: An introduction
MySQL Triggers named BEFORE INSERT are launched automatically before a table’s insert event.
The basic syntax for building a MySQL BEFORE INSERT trigger is demonstrated by the following:
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW Trigger_body;
In this syntax:
In the CREATE TRIGGER clause, first state the name of the trigger you wish to create.
To set the time to activate the trigger, use the BEFORE INSERT clause.
After the ON keyword, add the name of the table the trigger is connected to in the third place.
The trigger body, which contains one or more SQL statements that are executed when the trigger is triggered, should also be specified.
If the trigger body contains multiple statements, you must use the BEGIN END block and alter the default delimiter:
DELIMITER $$ CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
It should be noted that you can access and modify the NEW values in a BEFORE INSERT trigger. The OLD values, however, are inaccessible because they obviously don’t exist.
MySQL AFTER INSERT Trigger
To insert data into a table after inserting data into another table, you will learn how to create a MySQL AFTER INSERT trigger here.
The basics of MySQL’s AFTER INSERT triggers
After an insert event has been placed on a table, MySQL AFTER INSERT triggers is automatically triggered.
The fundamental syntax for writing a MySQL AFTER INSERT trigger is shown below:
CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW trigger_body
In this syntax:
After the CREATE TRIGGER keywords, provide the trigger you want to create its name.
To set the time to activate the trigger, use the AFTER INSERT clause.
After the ON keyword, add the name of the table on which you wish to construct the trigger in the third place.
The trigger body, which consists of one or more statements that run when the trigger is invoked, should then be specified.
You must use the BEGIN END block and alter the default delimiter if the trigger body contains multiple statements:
DELIMITER $$ CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
You can access the NEW values in an AFTER INSERT trigger, but you cannot modify them. Additionally, since there is no OLD on INSERT triggers, you cannot access the OLD values.
MySQL BEFORE UPDATE Trigger
In order to validate data before it is updated to a table, you will learn how to create a MySQL BEFORE UPDATE trigger here.
BEFORE UPDATE triggers in MySQL – An introduction
BEFORE UPDATE IN MySQL Before an update event occurs on the table linked to the triggers, the triggers are automatically triggered.
The syntax for building a BEFORE UPDATE trigger in MySQL is as follows:
CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name FOR EACH ROW Trigger_body
In this syntax:
After the CREATE TRIGGER keywords, provide the trigger you want to create its name.
Second, to select the moment to activate the trigger, use the BEFORE UPDATE clause.
After the ON keyword, add the name of the table the trigger belongs to in the third place.
The trigger body, which includes one or more statements, should then be specified.
The BEGIN END block must be used if the trigger body contains multiple statements. Additionally, you need to do the following to alter the default delimiter:
DELIMITER $$ CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
You can update the NEW values in a BEFORE UPDATE trigger, but not the OLD values.
MySQL AFTER UPDATE Trigger
Here you’ll discover how to build a MySQL AFTER UPDATE trigger that records table changes.
The basics of MySQL’s AFTER UPDATE triggers
After an update event takes place on the trigger-related table, MySQL AFTER UPDATE triggers is automatically triggered.
The syntax for building a MySQL AFTER UPDATE trigger is as follows:
CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW trigger_body
In this syntax:
In the CREATE TRIGGER clause, first state the name of the trigger you wish to create.
Second, to set the time to activate the trigger, use the AFTER UPDATE clause.
After the ON keyword, add the name of the table the trigger belongs to in the third place.
The trigger body, which may include one or more statements, should then be specified.
Use the BEGIN END block if the trigger body contains multiple statements. Additionally, as demonstrated in the code below, you must modify the default delimiter:
DELIMITER $$ CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
You can access OLD and NEW rows in an AFTER UPDATE trigger, but you cannot update them.
MySQL BEFORE DELETE Trigger
To add deleted entries to an archive table, you will learn how to set up a MySQL BEFORE DELETE trigger here.
BEFORE DELETE triggers in MySQL: An introduction
MySQL Triggers named BEFORE DELETE are launched automatically before a delete event takes place in a table.
The fundamental syntax for building a MySQL BEFORE DELETE trigger is as follows:
CREATE TRIGGER trigger_name BEFORE DELETE ON table_name FOR EACH ROW trigger_body
In this syntax:
After the Generate TRIGGER keywords, give the trigger you want to create its name first.
Second, state that the trigger is called immediately before a deletion event using the BEFORE DELETE clause.
After the ON keyword, add the name of the table the trigger is connected to in the third place.
The trigger body, which comprises one or more statements that go into effect when the trigger fires, should then be specified.
Keep in mind that if the trigger body contains many statements, you must use the BEGIN END block to enclose them and temporarily modify the default delimiter as shown below:
DELIMITER $$ CREATE TRIGGER trigger_name BEFORE DELETE ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
You can access the OLD row in a BEFORE DELETE trigger, but you cannot update it. Additionally, the BEFORE DELETE trigger’s NEW row is absent.
MySQL AFTER DELETE Trigger
To maintain a summary table of another table, you will learn how to establish a MySQL AFTER DELETE trigger here.
The basics of MySQL’s AFTER DELETE triggers
After a delete event takes place on a table, MySQL AFTER DELETE triggers is automatically triggered.
The fundamental syntax for building a MySQL AFTER DELETE trigger is as follows:
CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROW Trigger_body;
In this syntax:
In the CREATE TRIGGER clause, first state the name of the trigger you wish to create.
Second, to select the time to activate the trigger, use the AFTER DELETE clause.
After the ON keyword, add the name of the table the trigger belongs to in the third place.
The trigger body, which includes one or more statements that are executed when the trigger is triggered, should also be specified.
The default delimiter between $$ and; must be reversed if there are multiple statements in the trigger body, as demonstrated in the following:
DELIMITER $$ CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
You can access the OLD row in an AFTER DELETE trigger, but you cannot modify it.
Keep in mind that the AFTER DELETE trigger does not contain a NEW row.
