~ ~ 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 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 |
DROP TABLE IF EXISTS tab1; CREATE TABLE tab1( tab1_id varchar (11) ); |
Create a table TaB2
1234 |
DROP TABLE IF EXISTS tab2; CREATE TABLE tab2( 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 &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; insert into tab2 (tab2_id) values end |
Test it.
1 |
INSERT INTO tab1(tab1_id) values ( ‘0001‘ ); |
Look at the results
12 |
SELECT * FROM tab1; SELECT * FROM tab2; |
Example2:
Create Trigger: T_AFTERDELETE_ON_TAB1
Function: Delete the TAB1 table records automatically deleted the corresponding records in the TAB2 table
1234567 |
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.
1 |
DELETE FROM tab1 WHERE tab1_id= ‘0001‘ ; |
Look at the results
12 |
SELECT * FROM tab1; SELECT |
MySQL Trigger Simple instance