MySQL Trigger Simple instance

MySQL Trigger Simple instance

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 a time setting for execution: it can be set either before or after the event occurs.
{INSERT | UPDATE | DELETE} -the same can be set for triggered events: they can be triggered during an insert, update, or delete operation.
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

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

MySQL Trigger Simple instance