Article catalogue

• 1, MySQL trigger
• • 1. Concept of trigger
  • 2. Create trigger
  • 3. View triggers
  • 4. Delete trigger
  • 5. Summary of triggers

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