1. Trigger introduction

Triggers are database objects related to tables , Referring to insert / update / delete Before (BEFORE) Or after (AFTER), Trigger and execute the... Defined in the trigger SQL Statement set . This feature of trigger can help to ensure the integrity of data in database , logging , Data verification and other operations .

Use the alias OLD and NEW To refer to the changed record content in the trigger , This is similar to other databases . Now triggers only support row level triggering , Statement level triggering is not supported .

Trigger Type	NEW and OLD
INSERT Type trigger	NEW Indicates the data to be added or added
UPDATE Type trigger	OLD Represents the data before modification ,NEW Represents the data that will be or has been modified
DELETE Type trigger	OLD Data that will be or has been deleted

---

2. Trigger Syntax

---

2.1 Create trigger

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW --  Line level triggers 
BEGIN
	trigger_stmt;
END;

---

2.2 Check triggers

SHOW TRIGGERS;

---

2.3 Delete trigger

--  If not specified  schema_name, The default is the current database 
DROP TRIGGER [schema_name.]trigger_name ; 

---

3. Trigger case

Record by trigger tb_user Data change log for table , Insert the change log into the log table user_logs in , Including the addition of 、 modify 、 Delete ;

Table structure preparation :

--  preparation  :  Log table  user_logs
create table user_logs
(
    id             int(11)     not null auto_increment,
    operation      varchar(20) not null comment ' Operation type , insert/update/delete',
    operate_time   datetime    not null comment ' Operating time ',
    operate_id     int(11)     not null comment ' Operation of the ID',
    operate_params varchar(500) comment ' Operating parameters ',
    primary key (`id`)
) engine = innodb
  default charset = utf8;

Sample data preparation ：

create table tb_user
(
    id         int primary key auto_increment comment ' Primary key ',
    name       varchar(50) not null comment ' user name ',
    phone      varchar(11) not null comment ' cell-phone number ',
    email      varchar(100) comment ' mailbox ',
    profession varchar(11) comment ' major ',
    age        tinyint unsigned comment ' Age ',
    gender     char(1) comment ' Gender  , 1:  male , 2:  Woman ',
    status     char(1) comment ' state ',
    createtime datetime comment ' Creation time '
) comment ' System user table ';

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Lyu3 bu4 ', '17799990000', '[email protected]', ' Software Engineering ', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Cao Cao ', '17799990001', '[email protected]', ' Communication Engineering ', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' zhaoyun ', '17799990002', '[email protected]', ' English ', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' The Monkey King ', '17799990003', '[email protected]', ' Project cost ', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Hua mu LAN ', '17799990004', '[email protected]', ' Software Engineering ', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Big Joe ', '17799990005', '[email protected]', ' dance ', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Luna ', '17799990006', '[email protected]', ' Applied Mathematics ', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Cheng Yaojin ', '17799990007', '[email protected]', ' chemical ', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Xiang yu ', '17799990008', '[email protected]', ' Metal material ', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' White ', '17799990009', '[email protected]', ' Mechanical engineering and its automation   turn ', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Han xin ', '17799990010', '[email protected]', ' Inorganic nonmetallic material worker   cheng ', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Jingke ', '17799990011', '[email protected]', ' accounting ', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' The king of Lanling ', '17799990012', '[email protected]', ' Project cost ', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Berserker iron ', '17799990013', '[email protected]', ' Applied Mathematics ', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' The sable cicada ', '17799990014', '[email protected]', ' Software Engineering ', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Daji ', '17799990015', '[email protected]', ' Software Engineering ', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Mi month ', '17799990016', '[email protected]', ' industrial economy ', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Ying Zheng ', '17799990017', '[email protected]', ' chemical ', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Judge dee ', '17799990018', '[email protected]', ' International Trade ', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Angela ', '17799990019', '[email protected]', ' city planning ', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Dianwei ', '17799990020', '[email protected]', ' city planning ', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Lian po ', '17799990021', '[email protected]', ' Civil Engineering ', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Hou Yi ', '17799990022', '[email protected]', ' Urban landscape ', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES (' Jiang Ziya ', '17799990023', '[email protected]', ' Project cost ', 29, '1', '4', '2003-05-26 00:00:00');

---

3.1 Insert data trigger

create trigger tb_user_insert_trigger
    after insert
    on tb_user
    for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (null, 'insert', now(), new.id,
            concat(' The inserted data content is : id=', new.id, ',name=', new.name, ', phone=', NEW.phone, ', email=', NEW.email,
                   ', profession=', NEW.profession));
end;

test ：

--  see 
show triggers;

--  Insert data into tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (26, ' Second prince ', '18809091212', '[email protected]', ' Software Engineering ', 23, '1', '1', now());

see user_logs The data in it

After the test , Check whether the data in the log table can be inserted normally , And the correctness of the inserted data .

---

3.2 Modify data trigger

create trigger tb_user_update_trigger
    after update
    on tb_user
    for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (null, 'update', now(), new.id,
            concat(' Update previous data : id=', old.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email,
                   ', profession=', old.profession, ' |  The updated data : id=', new.id, ',name=', new.name, ', phone=',
                   NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

--  see 
show triggers;

--  to update 
update tb_user
set profession = ' accounting '
where id = 23;

update tb_user
set profession = ' accounting '
where id <= 5;

After the test , Check whether the data in the log table can be inserted normally , And the correctness of the inserted data .

---

3.3 Delete data trigger

create trigger tb_user_delete_trigger
    after delete
    on tb_user
    for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (null, 'delete', now(), old.id,
            concat(' Delete previous data : id=', old.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email,
                   ', profession=', old.profession));
end;

View new delete data triggers ：

show triggers;

--  Delete data 
delete
from tb_user
where id = 26;

After the test , Check whether the data in the log table can be inserted normally , And the correctness of the inserted data .

---

4. Summary

• MySQL View
• MySQL stored procedure Storage function

---

---