MySQL——触发器（trigger）基本结构

---

1、修改分隔符符号

delimiter $$

$$可以修改

2、创建触发器函数名称

create trigger 函数名 

3、什么样在操作触发，操作哪个表

after ：……之后触发 

before ：……之后触发 

insert ：……之后触发 

update ：……之后触发 

delete ：……之后触发 

---

on 表名

实例

after insert on user

4、for each row 声明每次触发都被执行

5、 开始触发器代码

begin

6、 触发器触发后执行代码块

7、 结束触发器

end ;

8、 修改分隔符

$$

delimiter ; 

DDL

CREATE TABLE user (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL UNIQUE,password VARCHAR(255) NOT NULL,email VARCHAR(100) UNIQUE,phone VARCHAR(20) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_wallet (wallet_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
CREATE TABLE user_wallet_log (log_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,transaction_type VARCHAR(50) NOT NULL,amount DECIMAL(10, 2) NOT NULL,transaction_time TIMESTAMP NOT NULL,FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);CREATE TABLE product_type (type_id INT AUTO_INCREMENT PRIMARY KEY,type_name VARCHAR(100) NOT NULL,parent_id INT NULL,description TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE product (product_id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(255) NOT NULL,price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,stock INT NOT NULL DEFAULT 0,type_id INT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (type_id) REFERENCES product_type(type_id) ON DELETE RESTRICT
);CREATE TABLE `order` (`order_id` INT AUTO_INCREMENT PRIMARY KEY,`user_id` INT NOT NULL,`order_status` VARCHAR(50) NOT NULL DEFAULT '待支付',`order_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,`payment_status` VARCHAR(50) NOT NULL DEFAULT '未支付',`payment_time` TIMESTAMP NULL,`total_price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT
);CREATE TABLE order_info (order_info_id INT AUTO_INCREMENT PRIMARY KEY,order_id INT NOT NULL,product_id INT NOT NULL,quantity INT NOT NULL,unit_price DECIMAL(10, 2) NOT NULL,FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT
);

DML

user相关

 
-- 插入10条用户数据
INSERT INTO `user` VALUES ('1', '王语嫣', 'password1', '[e-mail]', '13800000001', '2024-06-06 22:07:39');
INSERT INTO `user` VALUES ('2', '小龙女', 'password2', '[e-mail]', '13800000002', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES ('3', '赵灵儿', 'password3', '[e-mail]', '13800000003', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES ('4', '杨过', 'password4', '[e-mail]', '13800000004', '2024-05-31 22:07:39');
INSERT INTO `user` VALUES ('5', '向问天', 'password5', '[e-mail]', '13800000005', '2024-06-04 22:07:39');
INSERT INTO `user` VALUES ('6', '杨潇', 'password6', '[e-mail]', '13800000006', '2024-06-01 22:07:39');
INSERT INTO `user` VALUES ('7', '郭靖', 'password7', '[e-mail]', '13800000007', '2024-06-01 22:07:39');
INSERT INTO `user` VALUES ('8', '黄蓉', 'password8', '[e-mail]', '13800000008', '2024-06-04 22:07:39');
INSERT INTO `user` VALUES ('9', '程英', 'password9', '[e-mail]', '13800000009', '2024-06-08 22:07:39');
INSERT INTO `user` VALUES ('10', '郭襄', 'password10', '[e-mail]', '13800000010', '2024-06-08 22:07:39');-- 插入10条用户钱包数据，与user表对应
INSERT INTO `user_wallet` VALUES ('1', '1', '422.00', '2024-06-08 22:07:42');
INSERT INTO `user_wallet` VALUES ('2', '2', '897.00', '2024-06-04 22:07:42');
INSERT INTO `user_wallet` VALUES ('3', '3', '354.00', '2024-06-03 22:07:42');
INSERT INTO `user_wallet` VALUES ('4', '4', '758.00', '2024-06-07 22:07:42');
INSERT INTO `user_wallet` VALUES ('5', '5', '319.00', '2024-06-06 22:07:42');
INSERT INTO `user_wallet` VALUES ('6', '6', '350.00', '2024-06-08 22:07:42');
INSERT INTO `user_wallet` VALUES ('7', '7', '871.00', '2024-06-04 22:07:42');
INSERT INTO `user_wallet` VALUES ('8', '8', '515.00', '2024-06-05 22:07:42');
INSERT INTO `user_wallet` VALUES ('9', '9', '1042.00', '2024-06-05 22:07:42');
INSERT INTO `user_wallet` VALUES ('10', '10', '273.00', '2024-06-03 22:07:42');-- 插入10条用户钱包交易日志数据
INSERT INTO `user_wallet_log` VALUES ('1', '1', '充值', '100.00', '2024-06-01 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('2', '2', '消费', '50.00', '2024-06-08 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('3', '3', '提现', '200.00', '2024-06-09 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('4', '4', '充值', '150.00', '2024-05-31 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('5', '5', '消费', '75.00', '2024-06-04 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('6', '6', '提现', '300.00', '2024-06-02 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('7', '7', '充值', '250.00', '2024-06-09 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('8', '8', '消费', '120.00', '2024-06-07 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('9', '9', '提现', '400.00', '2024-06-08 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('10', '10', '充值', '300.00', '2024-05-31 22:07:48');

product相关

INSERT INTO `product_type` VALUES ('1', '智能手机', null, '包含各种品牌和型号的智能手机，涵盖不同价格区间和功能特点。', '2024-05-31 22:13:55');
INSERT INTO `product_type` VALUES ('2', '笔记本电脑', null, '提供多种类型和配置的笔记本电脑，适用于不同工作和学习需求。', '2024-06-01 22:13:55');
INSERT INTO `product_type` VALUES ('3', '智能家居', null, '包含智能家居设备，如智能音箱、智能照明、智能门锁等。', '2024-06-02 22:13:55');
INSERT INTO `product_type` VALUES ('4', '电视与显示器', null, '涵盖各种尺寸和分辨率的电视与显示器，适用于家庭和企业使用。', '2024-06-03 22:13:55');
INSERT INTO `product_type` VALUES ('5', '耳机与音响', null, '提供高品质耳机和音响设备，满足用户的音频体验需求。', '2024-06-04 22:13:55');
INSERT INTO `product` VALUES ('1', 'iPhone 13', '6999.00', '100', '1', '2024-06-05 22:13:55');
INSERT INTO `product` VALUES ('2', 'MacBook Pro', '14999.00', '50', '2', '2024-06-06 22:13:55');
INSERT INTO `product` VALUES ('3', '小米智能音箱', '199.00', '200', '3', '2024-06-07 22:13:55');
INSERT INTO `product` VALUES ('4', '三星65寸4K电视', '7999.00', '80', '4', '2024-06-08 22:13:55');
INSERT INTO `product` VALUES ('5', '索尼降噪耳机', '1299.00', '150', '5', '2024-06-09 22:13:55');
INSERT INTO `product` VALUES ('6', '华为MatePad Pro', '3999.00', '70', '2', '2024-05-31 22:13:55');
INSERT INTO `product` VALUES ('7', '小米米家智能灯泡', '79.00', '300', '3', '2024-06-01 22:13:55');
INSERT INTO `product` VALUES ('8', 'LG 27寸显示器', '1499.00', '120', '4', '2024-06-02 22:13:55');
INSERT INTO `product` VALUES ('9', '苹果AirPods Pro', '1599.00', '90', '5', '2024-06-03 22:13:55');
INSERT INTO `product` VALUES ('10', '一加9 Pro', '4999.00', '60', '1', '2024-05-30 22:13:55');

DQL 

增

delimiter $$  -- 分隔符
create trigger after_inster_user -- 创建触发器的名称
after insert on `user` -- 用户表执行插入操作之后被出发的触发器
for each row -- 每行数据都要进行触发
begin#insert into `user` values(0,'散兵','password88','[e-mail]','1234567890','2024-06-17 22:07:39');  #能添加但执行插入语句时报错1442 - Can't update table 'user' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.#1. MYSQL中触发器中不能对本表进行 insert ,update ,delete操作，以免递归循环触发#2. 对于update 只能用set进行操作，insert与delete只能借助第二张表才能实现需求，如果可以最好用存储过程代替触发器 #3. 注意唯一索引不能有重复值INSERT INTO user_wallet (user_id, balance)  -- 在user_wallet表中插入一行数据VALUES (NEW.user_id, 0.00);  -- 使用NEW.user_id作为新插入行的user_id，balance为0.00
end ;
$$
delimiter ; -- 结束$$的有效性更换成;

输出 

delimiter $$ 
create trigger after_insert_user
before insert on `user`
for each row
beginsignal sqlstate '45000' set message_text = '我们人为的停止了数据commit'; # signal sqlstate '45000' 代表错误，会停止操作，包含插入语句也会取消commit操作
end;
$$
delimiter $$

删 

delete from `user` where user_id=5;
# 报错，删除用户前需要删除从表的数据。
delimiter $$ 
create trigger delete_info_user
before delete on `user`
for each row
begindelete from user_wallet where user_id=old.user_id;delete from user_wallet_log where user_id=old.user_id;
end;
$$
delimiter ;delete from `user` where user_id=5;
#成功

改

delimiter $$ 
create trigger product_update_price
before update on product 
for each row
begin-- 编写代码的区域declare result decimal(10,4); -- 声明变量，必须在头部。if new.price=0 then signal sqlstate '45000' set message_text='新价格不能为0。';end if;set result =(new.price-old.price)/old.price*100;if abs(result)>10 then  -- abs():返回绝对值signal sqlstate '45000' set message_text = '价格上下浮动不能超过10%。';end if;
end;
$$
delimiter ;