MySQL - 在插入满足其他值的位置后触发更新另一个表中的列

2022-01-17 00:00:00 sql-update mysql triggers multiple-tables

第一次发帖，请多多包涵:

First time posting, so bear with me:

我有 3 个表:CWUsers、CWGamesList 和 CWOwnsGame.

I have got 3 tables: CWUsers, CWGamesList and CWOwnsGame.

CWUsers 包含用户名和他们通过购买的游戏获得的当前忠诚度积分.

CWUsers contains a users name and current loyalty points they have earned through games they have bought.

CWGamesList 包含游戏名称、游戏机类型以及用户在购买游戏时将获得的忠诚度积分.

CWGamesList contains the name of the game, the console type and the loyalty points that the user will receive when purchasing game.

CWOwnsGame 是一个事务表，其中记录了用户名、游戏名称和控制台类型.

CWOwnsGame is a transactions table, where it records the users name, the name of the game and the console type.

购买游戏后使用忠诚度积分更新 CWUsers 表时遇到问题.

Been having problems updating the CWUsers table with loyaltypoints after purchasing a game.

原来我在像这样插入 CWOwnsGame 后有一个触发器:

Originally I had a trigger after inserting into CWOwnsGame like this:

UPDATE CWUSERS SET loyaltyPoints = loyaltyPoints +
(SELECT loyaltyPoints FROM CWGameList WHERE gameConsole = "Wii" AND gameName = "Mario")
WHERE username = "bob"

虽然这仅适用于一个实例，但您如何使其适用于任何购买的游戏.

Although this only works for one instance, how would you make it work for any game that is purchased.

谢谢

推荐答案

在 row 触发器中，您可以访问正在修改的行的两个版本:OLD 包含 曾经 的行(如果存在)，NEW 包含该行因为它将是(如果它将存在.)

In a row trigger you have access to two versions of the row being modified: OLD contains the row as it was (if it existed), and NEW contains the row as it will be (if it will exist.)

为了使您的触发器按您描述的那样工作，您将使用 NEW 中的值来调节 WHERE 子句，就像这样:

To make your trigger work as you described, you would condition the WHERE clause using values from NEW, like so:

CREATE TRIGGER update_loyalty_points
  AFTER INSERT ON CWUserOwnsGame
  FOR EACH ROW
    UPDATE CWUsers
      SET loyaltyPoints = loyaltyPoints +
        (SELECT loyaltyPoints
          FROM CWGameList
          WHERE gameConsole = NEW.cName
            AND gameName = NEW.gName)
      WHERE username = NEW.uName;

我假设您已在表之间一致地命名列.请注意，NEW 中的正确列与 CWOwnsGame 中的列具有相同的名称.

I've assumed that you've named your columns consistently between tables. Note that the correct columns in NEW have the same names as the columns in CWOwnsGame.

相关文章