1, MySQL trigger

1. Concept of trigger

• Triggers are database objects related to tables. They can trigger and execute SQL statements defined in triggers before or after insert/update/delete. This feature of trigger can help the application in the database to ensure data integrity, logging, data verification and other operations.
• The aliases NEW and OLD are used to refer to the changed records in the trigger, which is similar to other databases. At present, triggers only support row level triggering, not statement level triggering.

2. Create trigger

• Standard grammar

DELIMITER $

CREATE TRIGGER Trigger Name 
BEFORE|AFTER INSERT|UPDATE|DELETE
ON Table name
[FOR EACH ROW]  -- row-level trigger 
BEGIN
	Function to be performed by trigger;
END$

DELIMITER ;

• Trigger demo. Record the data change log of the account table through the trigger. Including: add, modify, delete

  • Create account table

  -- establish db9 database
  CREATE DATABASE db9;
  
  -- use db9 database
  USE db9;
  
  -- Create account table account
  CREATE TABLE account(
  	id INT PRIMARY KEY AUTO_INCREMENT,	-- account id
  	NAME VARCHAR(20),					-- full name
  	money DOUBLE						-- balance
  );
  -- Add data
  INSERT INTO account VALUES (NULL,'Zhang San',1000),(NULL,'Li Si',2000);
  

  • Create log table

  -- Create log table account_log
  CREATE TABLE account_log(
  	id INT PRIMARY KEY AUTO_INCREMENT,	-- journal id
  	operation VARCHAR(20),				-- Operation type (insert update delete)
  	operation_time DATETIME,			-- Operation time
  	operation_id INT,					-- Operation table id
  	operation_params VARCHAR(200)       -- Operating parameters
  );
  

  • Create INSERT trigger

  -- establish INSERT trigger
  DELIMITER $
  
  CREATE TRIGGER account_insert
  AFTER INSERT
  ON account
  FOR EACH ROW
  BEGIN
  	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('After insertion{id=',new.id,',name=',new.name,',money=',new.money,'}'));
  END$
  
  DELIMITER ;
  
  -- towards account Add record to table
  INSERT INTO account VALUES (NULL,'Wang Wu',3000);
  
  -- query account surface
  SELECT * FROM account;
  
  -- Query log table
  SELECT * FROM account_log;
  

  • Create UPDATE trigger

  -- establish UPDATE trigger
  DELIMITER $
  
  CREATE TRIGGER account_update
  AFTER UPDATE
  ON account
  FOR EACH ROW
  BEGIN
  	INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('Before modification{id=',old.id,',name=',old.name,',money=',old.money,'}','After modification{id=',new.id,',name=',new.name,',money=',new.money,'}'));
  END$
  
  DELIMITER ;
  
  -- modify account surface
  UPDATE account SET money=3500 WHERE id=3;
  
  -- query account surface
  SELECT * FROM account;
  
  -- Query log table
  SELECT * FROM account_log;
  

  • Create DELETE trigger

  -- establish DELETE trigger
  DELIMITER $
  
  CREATE TRIGGER account_delete
  AFTER DELETE
  ON account
  FOR EACH ROW
  BEGIN
  	INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('Before deletion{id=',old.id,',name=',old.name,',money=',old.money,'}'));
  END$
  
  DELIMITER ;
  
  -- delete account Table data
  DELETE FROM account WHERE id=3;
  
  -- query account surface
  SELECT * FROM account;
  
  -- Query log table
  SELECT * FROM account_log;
  

3. View triggers

-- Standard grammar
SHOW TRIGGERS;

-- View triggers
SHOW TRIGGERS;

4. Delete trigger

-- Standard grammar
DROP TRIGGER Trigger Name ;

-- delete DELETE trigger
DROP TRIGGER account_delete;

5. Summary of triggers

• Triggers are database objects related to tables
• The SQL statement defined in the trigger can be triggered and executed before or after insert/update/delete
• This feature of trigger can help the application in the database to ensure data integrity, logging, data verification and other operations
• Use the aliases NEW and OLD to refer to the changed record content in the trigger