岗位实习触发器效果

创表语句

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    first_name VARCHAR(50) NOT NULL COMMENT '名',
    last_name VARCHAR(50) NOT NULL COMMENT '姓',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) UNIQUE COMMENT '电话',
    hire_date DATE NOT NULL COMMENT '入职日期'
);

CREATE TABLE `change_in_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation` varchar(15) NOT NULL COMMENT '操作类型',
  `operate_time` datetime NOT NULL COMMENT '操作时间',
  `operate_id` int(11) NOT NULL COMMENT '操作的ID',
  `operate_params` varchar(255) NOT NULL COMMENT '操作参数',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

触发器语句

案例一（insert） 

delimiter $$
create trigger after_insert_users
after insert on users
for each row
begin
insert into change_in_users (id,operation,operate_time,operate_id,operate_params)  values (null,'insert',now(),new.user_id,concat('插入数据内容为,','username=',new.username,'password=',new.`password`,'first_name=',new.first_name,'last_name=',new.last_name,'gender=',new.gender,'email=',new.email,'phone=',new.phone,'hire_date=',new.hire_date));
end ;
$$
delimiter ;

案例二 （update）

delimiter $$
create trigger after_update_users
after update on users
for each row
begin
insert into change_in_users (id,operation,operate_time,operate_id,operate_params)  values (null,'update',now(),new.user_id,concat('更新数据的变动为,','username:',old.username,'变为',new.username,'password:',old.`password`,'变为',new.`password`,'first_name:',old.first_name,'变为',new.first_name,'last_name:',old.last_name,'变为',new.last_name,'gender:',old.gender,'变为',new.gender,'email:',old.email,'变为',new.email,'phone:',old.phone,'变为',new.phone,'hire_date:',old.hire_date,'变为',new.hire_date));
end ;
$$
delimiter ;

案例三 (delete)

delimiter $$
create trigger after_delete_users
after delete on users
for each row
begin
insert into change_in_users (id,operation,operate_time,operate_id,operate_params)  values (null,'delete',now(),old.user_id,concat('删之前的数据','user_id:',old.user_id,'username:',old.username,'password:',old.`password`,'first_time:',old.first_name,'last_name:',old.last_name,'gender:',old.gender,'email:',old.email,'phone:',old.phone));
end ;
$$
delimiter ;

插入语句

INSERT INTO users (user_id, username, password, first_name, last_name, gender, email, phone, hire_date) VALUES
(1, '韦小宝', '123', '韦小宝', '假太监', '男', 'weixiaobao@example.com', '13800138001', '2020-01-01'),
(2, '孙悟空', '123', '孙悟空', '齐天大圣', '男', 'sunwukong@example.com', '13900139002', '2021-05-15'),
(3, '林黛玉', '123', '林黛玉', '潇湘妃子', '女', 'lingdaiyu@example.com', '13700137003', '2022-09-01'),
(4, '张三', '123', '张三', '张五', '男', '456123789@qq.com', '12345678912', '2020-06-06');

效果图：

users表：

change_in_users表：

更新语句

update users set username = '张飞',password = '520' where user_id = 4 ;

效果图：

users表：

change_in_users表：

删除语句：

delete from users where user_id = 4;

效果图：

users表：

change_in_users表：

查询所有触发器：

show triggers;

效果图：