• 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

    1. CREATE TABLE user (
    2.     user_id INT AUTO_INCREMENT PRIMARY KEY,
    3.     username VARCHAR(50) NOT NULL UNIQUE,
    4.     password VARCHAR(255) NOT NULL,
    5.     email VARCHAR(100) UNIQUE,
    6.     phone VARCHAR(20) UNIQUE,
    7.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    8. );
    9. CREATE TABLE user_wallet (
    10.     wallet_id INT AUTO_INCREMENT PRIMARY KEY,
    11.     user_id INT NOT NULL,
    12.     balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    13.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    14.     FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
    15. );
    16. CREATE TABLE user_wallet_log (
    17.     log_id INT AUTO_INCREMENT PRIMARY KEY,
    18.     user_id INT NOT NULL,
    19.     transaction_type VARCHAR(50) NOT NULL,
    20.     amount DECIMAL(10, 2) NOT NULL,
    21.     transaction_time TIMESTAMP NOT NULL,
    22.     FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
    23. );
    24.  
    25. CREATE TABLE product_type (
    26.     type_id INT AUTO_INCREMENT PRIMARY KEY,
    27.     type_name VARCHAR(100) NOT NULL,
    28.     parent_id INT NULL,
    29.     description TEXT,
    30.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    31. );
    32. CREATE TABLE product (
    33.     product_id INT AUTO_INCREMENT PRIMARY KEY,
    34.     product_name VARCHAR(255) NOT NULL,
    35.     price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    36.     stock INT NOT NULL DEFAULT 0,
    37.     type_id INT NOT NULL,
    38.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    39.     FOREIGN KEY (type_id) REFERENCES product_type(type_id) ON DELETE RESTRICT
    40. );
    41.  
    42. CREATE TABLE `order` (
    43.     `order_id` INT AUTO_INCREMENT PRIMARY KEY,
    44.     `user_id` INT NOT NULL,
    45.     `order_status` VARCHAR(50) NOT NULL DEFAULT '待支付',
    46.     `order_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    47.     `payment_status` VARCHAR(50) NOT NULL DEFAULT '未支付',
    48.     `payment_time` TIMESTAMP NULL,
    49.     `total_price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    50.     FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT
    51. );
    52.  
    53. CREATE TABLE order_info (
    54.     order_info_id INT AUTO_INCREMENT PRIMARY KEY,
    55.     order_id INT NOT NULL,
    56.     product_id INT NOT NULL,
    57.     quantity INT NOT NULL,
    58.     unit_price DECIMAL(10, 2) NOT NULL,
    59.     FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,
    60.     FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT
    61. );

    DML

    user相关

    1.  
    2. -- 插入10条用户数据
    3. INSERT INTO `user` VALUES ('1', '王语嫣', 'password1', 'user1@example.com', '13800000001', '2024-06-06 22:07:39');
    4. INSERT INTO `user` VALUES ('2', '小龙女', 'password2', 'user2@example.com', '13800000002', '2024-06-09 22:07:39');
    5. INSERT INTO `user` VALUES ('3', '赵灵儿', 'password3', 'user3@example.com', '13800000003', '2024-06-09 22:07:39');
    6. INSERT INTO `user` VALUES ('4', '杨过', 'password4', 'user4@example.com', '13800000004', '2024-05-31 22:07:39');
    7. INSERT INTO `user` VALUES ('5', '向问天', 'password5', 'user5@example.com', '13800000005', '2024-06-04 22:07:39');
    8. INSERT INTO `user` VALUES ('6', '杨潇', 'password6', 'user6@example.com', '13800000006', '2024-06-01 22:07:39');
    9. INSERT INTO `user` VALUES ('7', '郭靖', 'password7', 'user7@example.com', '13800000007', '2024-06-01 22:07:39');
    10. INSERT INTO `user` VALUES ('8', '黄蓉', 'password8', 'user8@example.com', '13800000008', '2024-06-04 22:07:39');
    11. INSERT INTO `user` VALUES ('9', '程英', 'password9', 'user9@example.com', '13800000009', '2024-06-08 22:07:39');
    12. INSERT INTO `user` VALUES ('10', '郭襄', 'password10', 'user10@example.com', '13800000010', '2024-06-08 22:07:39');
    13.  
    14.  
    15. -- 插入10条用户钱包数据，与user表对应
    16. INSERT INTO `user_wallet` VALUES ('1', '1', '422.00', '2024-06-08 22:07:42');
    17. INSERT INTO `user_wallet` VALUES ('2', '2', '897.00', '2024-06-04 22:07:42');
    18. INSERT INTO `user_wallet` VALUES ('3', '3', '354.00', '2024-06-03 22:07:42');
    19. INSERT INTO `user_wallet` VALUES ('4', '4', '758.00', '2024-06-07 22:07:42');
    20. INSERT INTO `user_wallet` VALUES ('5', '5', '319.00', '2024-06-06 22:07:42');
    21. INSERT INTO `user_wallet` VALUES ('6', '6', '350.00', '2024-06-08 22:07:42');
    22. INSERT INTO `user_wallet` VALUES ('7', '7', '871.00', '2024-06-04 22:07:42');
    23. INSERT INTO `user_wallet` VALUES ('8', '8', '515.00', '2024-06-05 22:07:42');
    24. INSERT INTO `user_wallet` VALUES ('9', '9', '1042.00', '2024-06-05 22:07:42');
    25. INSERT INTO `user_wallet` VALUES ('10', '10', '273.00', '2024-06-03 22:07:42');
    26.  
    27.  
    28.  
    29. -- 插入10条用户钱包交易日志数据
    30. INSERT INTO `user_wallet_log` VALUES ('1', '1', '充值', '100.00', '2024-06-01 22:07:48');
    31. INSERT INTO `user_wallet_log` VALUES ('2', '2', '消费', '50.00', '2024-06-08 22:07:48');
    32. INSERT INTO `user_wallet_log` VALUES ('3', '3', '提现', '200.00', '2024-06-09 22:07:48');
    33. INSERT INTO `user_wallet_log` VALUES ('4', '4', '充值', '150.00', '2024-05-31 22:07:48');
    34. INSERT INTO `user_wallet_log` VALUES ('5', '5', '消费', '75.00', '2024-06-04 22:07:48');
    35. INSERT INTO `user_wallet_log` VALUES ('6', '6', '提现', '300.00', '2024-06-02 22:07:48');
    36. INSERT INTO `user_wallet_log` VALUES ('7', '7', '充值', '250.00', '2024-06-09 22:07:48');
    37. INSERT INTO `user_wallet_log` VALUES ('8', '8', '消费', '120.00', '2024-06-07 22:07:48');
    38. INSERT INTO `user_wallet_log` VALUES ('9', '9', '提现', '400.00', '2024-06-08 22:07:48');
    39. INSERT INTO `user_wallet_log` VALUES ('10', '10', '充值', '300.00', '2024-05-31 22:07:48');

    product相关

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

    DQL 

    增

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

    输出 

    1. delimiter $$ 
    2. create trigger after_insert_user
    3. before insert on `user`
    4. for each row
    5. begin
    6.    signal sqlstate '45000' set message_text = '我们人为的停止了数据commit'; 
    7. 	 # signal sqlstate '45000' 代表错误，会停止操作，包含插入语句也会取消commit操作
    8. end;
    9. $$
    10. delimiter $$

    删 

    1.  
    2. delete from `user` where user_id=5;
    3. # 报错，删除用户前需要删除从表的数据。
    4. delimiter $$ 
    5. create trigger delete_info_user
    6. before delete on `user`
    7. for each row
    8. begin
    9. 	delete from user_wallet where user_id=old.user_id;
    10. 	delete from user_wallet_log where user_id=old.user_id;
    11. end;
    12. $$
    13. delimiter ;
    14.  
    15. delete from `user` where user_id=5;
    16. #成功

    改

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

  • 相关阅读:
    【探索Linux】—— 强大的命令行工具 P.14（进程间通信 | 匿名管道 | |进程池 | pipe() 函数 | mkfifo() 函数）
    单元测试（JUint）
    EasyExcel的简单读取操作
    浅谈 MySQL 新的身份验证插件 caching_sha2_password
    maven settings.xml文件（包含了配置阿里云镜像）
    python造测试数据存到excel
    问题 java.net.SocketException Too many open files
    项目（智慧教室）第四部分，页面交互功能，WebServer建立与使用，
    谈谈Selenium中浏览器驱动的日志
    SpringBoot(五) - Java8 新特性
  • 原文地址：https://blog.csdn.net/bengbeng2345/article/details/139743607