MySQL trigger detailed explanation and simple example

MySQL trigger simple example

grammar

CREATE TRIGGER <trigger name> -- Triggers must have a name, up to 64 characters, 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> --The 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 for 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.

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

Examples

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 a trigger: t_afterinsert_on_tab1

Function: After adding records in the tab1 table, the records will be automatically added 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 out

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

See the results

SELECT * FROM tab1;
SELECT * FROM tab2;

example2:

Create trigger: t_afterdelete_on_tab1

Function: 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 it out

DELETE FROM tab1 WHERE tab1_id='0001';

See the results

SELECT * FROM tab1;
SELECT * FROM tab2;

Thank you for reading, I hope it can help you, thank you for your support of this site!