Turn: MySQL Trigger notes

MySQL Trigger Simple instance

~ ~ Grammar ~ ~

CREATE TRIGGER < trigger name > --the trigger must have a name, a maximum of 64 characters, and may be appended with a delimiter. It is basically like naming 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 triggered events: they can be triggered during INSERT, update, or delete execution.
On < table name > --triggers are part of a table: triggers are activated when an INSERT, update, or delete operation is performed on the table. We cannot schedule two triggers for the same event in the same table.
for each ROW -The execution interval of the trigger: 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 you want to trigger: The statement here can be any legitimate statement, including compound statements, but the statements here are constrained by the same limitations as functions.

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

~ ~ Example ~ ~

example1:

Create a table Tab1

1234	DROPTABLE IF EXISTS tab1;CREATE TABLEtab1(    tab1_id varchar(11));

Create a table TaB2

1234	DROPTABLE IF EXISTS tab2;CREATE TABLEtab2(    tab2_id varchar(11));

Create trigger:t_afterinsert_on_tab1

Function: Automatically adds records to the TAB2 table after adding TAB1 table records

1234567

DROPTRIGGER IF EXISTS t_afterinsert_on_tab1;CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1FOR EACH ROWBEGIN     insert intotab2(tab2_id) values(new.tab1_id);END;

Test it.

1	INSERTINTOtab1(tab1_id) values(‘0001‘);

Look at the results

12	SELECT* FROM tab1;SELECT * FROMtab2;

example2:

Create trigger:t_afterdelete_on_tab1

Function: Delete the TAB1 table records automatically deleted the corresponding records in the TAB2 table

1234567

DROPTRIGGER IF EXISTS t_afterdelete_on_tab1;CREATE TRIGGER t_afterdelete_on_tab1AFTER DELETE ON tab1FOR EACH ROWBEGIN      delete from tab2 wheretab2_id=old.tab1_id;END;

Test it.

1	DELETEFROM tab1 WHEREtab1_id=‘0001‘;

Look at the results

12	SELECT* FROM tab1;SELECT * FROMtab2;

Turn: MySQL Trigger notes