时间戳

首页 > 解决方案 > 创建触发器以在更新同一表中的特定列后更新某些列

sql - 创建触发器以在更新同一表中的特定列后更新某些列

问题描述

我有一个名为 BrrowedBookBy 的表，这里是表描述

+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| ISBN_B             | char(50)    | YES  |     | NULL    |       |
| PIN_M              | int(11)     | YES  |     | NULL    |       |
| StartDate          | date        | YES  |     | NULL    |       |
| EndDate            | date        | YES  |     | NULL    |       |
| BrrowBookCondition | tinyint(1)  | YES  |     | NULL    |       |
| Late               | tinyint(1)  | YES  |     | NULL    |       |
| ReNew              | tinyint(1)  | YES  |     | NULL    |       |
| Fine               | int(11)     | YES  |     | NULL    |       |
| Fine_Description   | varchar(30) | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
9 rows in set (0.040 sec)

所以我想在更新 BorrowBookCondition 列时设置触发器

我试过这样但没有在玛丽亚工作

CREATE DEFINER=`root`@`localhost` TRIGGER Set_fine
  AFTER Update ON BorrowedBookBy
   FOR EACH ROW
    UPDATE Fine SET Fine = 50 and fine_description = “Not Meet The Condition”
      where BrrowBookCondition in (select * from BorrowedBookBy where BrrowBookCondition =0);

如果我在没有任何条件的情况下输入它，它就可以正常工作

CREATE DEFINER=`root`@`localhost` TRIGGER Set_fine
  AFTER Update ON BorrowedBookBy
   FOR EACH ROW
    UPDATE Fine SET Fine = 50 and fine_description = “Not Meet The Condition”;

标签： sqlmariadb

解决方案

我很困惑。您的触发器指的是另一个名为的表Fine，但您没有提到这一点。

您确定不想在要更新的同一行中设置它吗？

如果是这样：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER Set_fine BEFORE Update ON BorrowedBookBy
FOR EACH ROW
BEGIN
    IF NEW.BrrowBookCondition = 0 THEN
        SET Fine = 50;
        SET fine_description = 'Not Meet The Condition;
    END IF;
END;

DELIMITER ;

推荐阅读