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', 'user1@example.com', '13800000001', '2024-06-06 22:07:39');
INSERT INTO `user` VALUES ('2', '小龙女', 'password2', 'user2@example.com', '13800000002', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES ('3', '赵灵儿', 'password3', 'user3@example.com', '13800000003', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES ('4', '杨过', 'password4', 'user4@example.com', '13800000004', '2024-05-31 22:07:39');
INSERT INTO `user` VALUES ('5', '向问天', 'password5', 'user5@example.com', '13800000005', '2024-06-04 22:07:39');
INSERT INTO `user` VALUES ('6', '杨潇', 'password6', 'user6@example.com', '13800000006', '2024-06-01 22:07:39');
INSERT INTO `user` VALUES ('7', '郭靖', 'password7', 'user7@example.com', '13800000007', '2024-06-01 22:07:39');
INSERT INTO `user` VALUES ('8', '黄蓉', 'password8', 'user8@example.com', '13800000008', '2024-06-04 22:07:39');
INSERT INTO `user` VALUES ('9', '程英', 'password9', 'user9@example.com', '13800000009', '2024-06-08 22:07:39');
INSERT INTO `user` VALUES ('10', '郭襄', 'password10', 'user10@example.com', '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','sanbing@qq.com','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
begin
signal 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
begin
delete 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 ;