赞
踩
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 ;
- 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
- );
-
- -- 插入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');
- 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');
- 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 ;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。