1, Mysql trigger creation

1. Mysql trigger syntax

CREATE　[DEFINER = { 'user' | CURRENT_USER }]　
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body

2. Keyword interpretation in MySQL creation syntax

field	meaning	Possible values
DEFINER=	Optional parameter, specify creator, default to CURRENT_USER; The trigger will be executed by the user specified by this parameter, so the permission problem needs to be considered; DEFINER=‘root@%’	DEFINER=CURRENT_USER
trigger_name	Trigger name, preferably composed of table name + trigger event keyword + trigger time keyword;
trigger_time	Trigger time, before or after an event;	BEFORE,AFTER
trigger_event	Trigger events, such as triggered during insertion and deletion; INSERT: INSERT operation trigger, triggered when INSERT, LOAD DATA, REPLACE; UPDATE: trigger of UPDATE operation, triggered during UPDATE operation; DELETE: trigger for DELETE operation; triggered during DELETE and REPLACE operations;	INSERT,UPDATE,DELETE
table_name	Table name of triggering operation time;
trigger_order	Optional parameter. If multiple triggers with the same trigger event and touch time are defined (such as BEFORE UPDATE), the default trigger sequence is consistent with the creation sequence of triggers. You can use this parameter to change their trigger sequence. Since mysql 5.7.2, this parameter FOLLOWS has been supported: the currently created trigger is activated after the existing trigger; Precides: the trigger currently created is activated before the existing trigger;	FOLLOWS,PRECEDES
trigger_body	The content of the SQL statement that triggers execution, generally starts with begin and ends with end	begin ... end

3. MySQL DELIMITER

MySQL uses ";" by default The SQL statement encountered ';' as a DELIMITER Will submit. There may be multiple ";" in our trigger In order to prevent premature submission of trigger creation statements, we need to temporarily modify the MySQL separator, and then change the separator back after creation. Use DELIMITER to modify the DELIMITER

DELIMITER $
... 	-- Trigger creation statement;
$   	-- Submit create statement;
DELIMITER ;

3. Case

Create test tables and triggers

-- Create test table
DROP TABLE IF EXISTS `test_material`;
CREATE TABLE `test_material` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary key, auto increment',
  `test_code` varchar(100) DEFAULT '' COMMENT 'Test number',
  `test_name` varchar(100) DEFAULT '' COMMENT 'Test name',
  `test_quantity` int(11) DEFAULT '5000' comment 'quantity:5000 by default'
) engine=innodb DEFAULT charset=utf8 comment='Test table';

-- Create trigger
delimiter $
DROP TRIGGER IF EXISTS after_material$
CREATE TRIGGER after_material 
AFTER INSERT ON test1.test_material
FOR EACH ROW
BEGIN
 INSERT test2.test_material(test_code, test_name, test_quantity)
 VALUES(new.test_code, new.test_name, test_quantity);
END$
delimiter ;

Test trigger synchronization effect

INSERT INTO test1.test_material (test_code, test_name, test_quantity)
 VALUES ('533-0000-103', 'Fast diode(Stock used up item No. void T-0958-8)', 1000);

Result: test1 test_ While inserting data into material, test2 test_ Material also inserts the same data

-- View triggers created by the current database
SELECT * from information_schema.`TRIGGERS`;

-- Delete trigger 
DROP TRIGGER after_material;

Reference link: http://aiezu.com/article/mysql_trigger_syntax.html.