A Simple Example of MySQL Trigger Definition and Usage

This article illustrates the definition and usage of MySQL triggers. Share it for your reference, as follows:

Grammar

CREATE TRIGGER Trigger Name-Triggers must have a first name, up to 64 characters, possibly followed by a delimiter. It is basically similar to the naming of other objects in MySQL.
{BEFORE AFTER}--Triggers have execution time settings: they can be set to either before or after an event.
{INSERT UPDATE DELETE}-You can also set the events to be fired: they can be fired during the execution of insert, update, or delete.
ON Table Name-Triggers belong to one table: Insert, update, or delete operations on this table cause triggers to activate. We cannot schedule two triggers for the same event on the same table.
FOR EACH ROW-Trigger execution interval: The FOR EACH ROW clause tells the trigger to perform an action once every 1 row instead of once on the entire table.
< Trigger SQL statement > The trigger contains the SQL statement to be fired: the statement here can be any legal statement, including compound statements, but the statement here is subject to 1 kind of restrictions and functions.

Instance preparation

--  Create a table tab1
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
  tab1_id varchar(11)
);
--  Create a table tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
  tab2_id varchar(11)
);

Example 1: Adding one table triggers the addition of another table

--  Create Triggers :t_afterinsert_on_tab1
--  Role: Increase tab1 Table records are automatically added to the tab2 In the table 
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
   insert into tab2(tab2_id) values(new.tab1_id);
END;
--  Test 1 Under 
INSERT INTO tab1(tab1_id) values('0001');
--  Look at the results 
SELECT * FROM tab1;
SELECT * FROM tab2

Example 2: Deleting one table triggers the deletion of another table

--  Create Triggers :t_afterdelete_on_tab1
--  Function: Delete tab1 After the table is recorded, automatically set the tab2 The corresponding records in the table are deleted 
DROP TRIGGER IF EXISTS t_afterdelete_on_tab1;
CREATE TRIGGER t_afterdelete_on_tab1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
   delete from tab2 where tab2_id=old.tab1_id;
END;
--  Test 1 Under 
DELETE FROM tab1 WHERE tab1_id='0001';
--  Look at the results 
SELECT * FROM tab1;
SELECT * FROM tab2;

Example 3: Update one table, triggering the update of another table

--  Create Triggers :t_afterupdate_on_tab1
--  Function: Modify tab1 After the table is recorded, automatically set the tab2 Update the corresponding record in the table 
DROP TRIGGER IF EXISTS t_afterupdate_on_tab1;
CREATE TRIGGER t_afterupdate_on_tab1
AFTER UPDATE ON tab1
FOR EACH ROW
BEGIN
   update tab2 set tab2_id=new.tab1_id where tab2_id=old.tab1_id;
END;
--  Test 1 Under 
update tab1 set tab1_id='0002' WHERE tab1_id='0001';
--  Look at the results 
SELECT * FROM tab1;
SELECT * FROM tab2;

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.