Simple MySQL trigger instance

~~ Syntax ~~
 

CreateTrigger<Trigger name> -- The Trigger must have a name and a maximum of 64 characters. It may be followed by a separator. It is similar to the naming method of other objects in MySQL.
{Before|After} -- Trigger has the execution time setting: it can be set to before or after an event occurs.
{Insert|Update|Delete} -- The trigger events can also be set: they can be triggered during the execution of insert, update, or delete.
On<Table Name> -- A trigger belongs to a table. When an insert, update, or delete operation is performed on the table, the trigger is activated. we cannot schedule two triggers for the same event of the same table.
ForEach Row -- Trigger execution interval: The for each row clause notifies the trigger to execute an action on every row instead of 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 statements here are subject to the same restrictions as the functions.

-- You must have considerable permissions to create a trigger. If you are a root user, this is enough. This is different from the SQL standard.

 
~~ Instance ~~
 

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

Purpose: automatically add a record to the tab2 table after adding a record to the tab1 table

 
Drop trigger if exists t_afterinsert_on_tab1; Create trigger t_afterinsert_on_tab1 after insert on tab1for each rowbegin insert into tab2 (tab2_id) values (New. tab1_id); end;
 

Test

 
Insert into tab1 (tab1_id) values ('20140901 ');
 

View results

 
Select * From tab1; select * From tab2;
 

Example2:

Create a trigger:T_afterdelete_on_tab1

Purpose: Delete the records in Table tab1 and automatically delete the records in Table tab2.

Drop trigger if exists t_afterdelete_on_tab1; Create trigger t_afterdelete_on_tab1after delete on tab1for each rowbegin Delete from tab2 where tab2_id = old. tab1_id; end;
 

Test

 
Delete from tab1 where tab1_id = '20140901 ';
 

View results

 
Select * From tab1; select * From tab2;