MySQL Advanced 8 - Use of Triggers

A trigger is a special stored procedure that triggers execution when inserting, deleting, or modifying data in a specific table. It has more sophisticated and complex data control capabilities than the standard functions of the database itself.

It has the following characteristics:

Monitoring location: usually the table name

Monitoring event: update/delete/insert

Trigger time: after/ before

Trigger event: update/delete/insert

It cannot be called directly and is actively executed by the database.

example1:

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)
);

Create trigger: t_afterinsert_on_tab1

Function: Add tab1 table After recording, automatically add the record 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 it

INSERT INTO tab1(tab1_id) values('0001');

See the result

SELECT * FROM tab1;
SELECT * FROM tab2;

example2:

Create trigger: t_afterdelete_on_tab1

Function: After deleting the tab1 table record, the corresponding record 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 it

DELETE FROM tab1 WHERE tab1_id='0001';

Look at the results

SELECT * FROM tab1;
SELECT * FROM tab2;

The above is the content of MySQL Advanced 8 - the use of triggers. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!