MySQL trigger definition and usage simple example

This article describes the definition and usage of MySQL triggers. Share with you for your reference, the details are as follows:

grammar

CREATE TRIGGER trigger name - A trigger must have a name, up to 64 characters long, which may be followed by separators. It is named much like other objects in MySQL.
{ BEFORE | AFTER } -- The trigger has an execution time setting: it can be set before or after the event occurs.
{ INSERT | UPDATE | DELETE } -- You can also set the events that trigger: they can be triggered during the execution of insert, update or delete.
ON table name - A trigger belongs to a certain table: when an insert, update or delete operation is performed on this table, the trigger is activated. We cannot assign two triggers to the same event on the same table.
FOR EACH ROW -- trigger execution interval: FOR EACH ROW clause tells the trigger to perform the action every other row, rather than once for the entire table.
< Trigger SQL statement > --The trigger contains the SQL statement to be triggered: the statement here can be any legal statement, including compound statements, but the restrictions on the statements here are the same as those of functions.

Example Preparation

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

Example 1: Adding a new entry triggers the addition of another table

-- Create trigger: t_afterinsert_on_tab1
-- Effect: After adding records to the tab1 table, automatically add records to the tab2 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 INSERT INTO tab1(tab1_id) values('0001');
-- Look at the results SELECT * FROM tab1;
SELECT * FROM tab2

Example 2: Deleting one entry triggers deletion of another table

-- Create trigger: t_afterdelete_on_tab1
-- Effect: After deleting the records in the tab1 table, the corresponding records in the tab2 table will be automatically 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 DELETE FROM tab1 WHERE tab1_id='0001';
-- Look at the results SELECT * FROM tab1;
SELECT * FROM tab2;

Example 3: Update one row to trigger the update of another table

-- Create trigger: t_afterupdate_on_tab1
-- Effect: After modifying the records in the tab1 table, automatically update the corresponding records in the tab2 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 update tab1 set tab1_id='0002' WHERE tab1_id='0001';
-- Look at the results SELECT * FROM tab1;
SELECT * FROM tab2;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.