MySQL trigger instance demonstration

The following articles mainly introduce simple examples of MySQL triggers. This article mainly introduces the practical application of MySQL triggers based on related syntaxes. The following describes the specific content of this article, I hope it will help you learn MySQL triggers.

Syntax

The create trigger <TRIGGER Name> TRIGGER must have a name of up to 64 characters, which may be followed by a separator. It is similar to the naming method of other objects in MySQL.

{BEFORE | AFTER} triggers have execution time settings: they can be set BEFORE or AFTER an event occurs.

{INSERT | UPDATE | DELETE} can also set trigger events: they can be triggered during insert, update, or delete execution.

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.

Execution interval of the for each row trigger: The for each row clause notifies the MySQL trigger to execute an action every ROW, instead of executing an action 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 statements here are subject to the same restrictions as those of functions.

You must have considerable permissions to CREATE the MySQL trigger create 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 MySQL 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 tab1  
  
  
FOR EACH ROW  
  
  
BEGIN  
  
  
insert into tab2(tab2_id) values(new.tab1_id);  
  
  
END 
 
 

Test

 
 

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

View results

 
 

  
  
SELECT * FROM tab1;  
  
  
SELECT * FROM tab2; 
 
 

Example2:

Create 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_tab1  
  
  
AFTER DELETE ON tab1  
  
  
FOR EACH ROW  
  
  
BEGIN  
  
  
delete from tab2 where tab2_id=old.tab1_id;  
  
  
END; 
 
 

Test

 
 

  
  
DELETE FROM tab1 WHERE tab1_id='0001'; 
 
 

View results

 
 

  
  
SELECT * FROM tab1;  
  
  
SELECT * FROM tab2; 
 
 

The above content is

A Brief Introduction to MySQL triggers.