Detailed explanation of MySQL triggers

This article mainly introduces the relevant information of MySQL triggers Detailed explanation and simple examples. Friends in need can refer to

MySQL trigger simple examples

Syntax

CREATE TRIGGER --The trigger must have a name, up to 64 characters, possible It will be followed by a delimiter. It is basically similar to the naming method of other objects in MySQL.

{ BEFORE | AFTER } -- Trigger There is an execution time setting: it can be set before or after the event occurs.

{ INSERT | UPDATE | DELETE } --You can also set triggered events: they can be during the execution of insert, update or delete medium trigger.

ON

--The trigger belongs to a certain table: when an insert, update or delete is performed on this table The trigger is activated during operation. We cannot arrange two triggers for the same event in the same table.

FOR EACH ROW --Trigger execution interval: The FOR EACH ROW clause notifies the trigger to perform an 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 here The statements are subject to the same restrictions as function.

--You must have considerable permissions to create a trigger (CREATE TRIGGER). If you are already a Root user, that is enough. This is different from the SQL standard.

Example

##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: After adding tab1 table records, automatically add the records to 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 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';

See the result

SELECT * FROM tab1;
SELECT * FROM tab2;

The above is the detailed content of Detailed explanation of MySQL triggers. For more information, please follow other related articles on the PHP Chinese website!