如何使用before insert触发器来分配外键值

Question

我有一个这样的场景：

有两个表table1和table2。table1有一个主键pkey，table2有一个外键fkey。现在，在插入时，如果提供了外键，则该值应按原样插入。否则，必须使用某些计算从table1获取主键，并确定要插入的外键。我该怎么做？

我正在使用MySql 5.0。

编辑：

在我的场景中，table1保存账单详细信息，即table1具有账单和应付总金额。客户支付总未付余额的一部分或将为特定账单支付。我想要做的是，在没有提供bill_id（它是table1中的主键和table2中的外键）的情况下，在table1中搜索最早到期的账单并扣除到期金额，如有剩余则从下一个账单开始扣除。我想在数据库层面上实现这一点，而不是在上层实现。因此，当进行插入时，如果没有外键的值，则触发器或其他方式应检索并放置该值。我该如何实现？

使用这里提供的答案，我尝试了这个：

CREATE DEFINER=`root`@`localhost` TRIGGER `inflow_pay_done_insert` BEFORE INSERT ON `inflow_pay_done` FOR EACH ROW BEGIN
    DECLARE pkey INT;
    SET pkey = (SELECT bill_id from inflow_bills where payment_stat = 0 and rs_id = NEW.rs_id order by time_stamp limit 1);
    SET NEW.bill_id = IF(NEW.bill_id , NEW.bill_id , pkey);
    UPDATE raw_mat_sup rms SET rms.outstanding_bal_payable = rms.outstanding_bal_payable - NEW.amount where rms.rs_id = NEW.rs_id;
END|

我在尝试向inflow_pay_done插入数据时遇到以下错误：

/* SQL错误（1048）：无法将'bill_id'列设置为null */

- Pruthvi Nag

---

如果在table2的INSERT语句中没有提供table1的主键，那么从table1选择主键的条件是什么？只是为了建立有效的关系/外键而随机选择一个吗？ - Kaii

换句话说，你能更明确地表达你想要实现什么吗？ - Kaii

Answer 1

你可以在 BEFORE INSERT 触发器中使用子查询来实现这个功能。

DELIMITER |
DROP TRIGGER `inflow_pay_done_insert`|
CREATE TRIGGER `inflow_pay_done_insert` BEFORE INSERT ON `inflow_pay_done`
FOR EACH ROW
BEGIN
  UPDATE raw_mat_sup rms 
    SET rms.outstanding_bal_payable = rms.outstanding_bal_payable - NEW.amount 
    WHERE rms.rs_id = NEW.rs_id;
  NEW.bill_id = IF(NEW.bill_id, 
    /* if "bill_id" is provided in INSERT statement, use provided value */
    NEW.bill_id, 
    /* if not, query other table for the correct value */
    (  /* this subquery is just an example, put your own query here*/
       SELECT bill_id FROM inflow_bills 
       /* find customers newest bill based on newest date and customer id */
       WHERE payment_stat = 0 AND rs_id = NEW.rs_id
       ORDER BY time_stamp DESC LIMIT 1
    )
  );
END;
|
delimiter;

更新

由于MySQL Bug的存在，这只适用于允许列为空且没有列约束（->外键）的情况。原因是MySQL与其他DBMS不同，在执行BEFORE INSERT触发器之前检查约束，并有效地避免了触发器的执行，而该触发器将纠正要插入的数据。

在MySQL行为改变之前，唯一的解决方案是使用STORED PROCEDURE而不是普通的INSERT。然后使用应插入的值调用存储过程。在存储过程中，进行数据校正（例如：选择正确的bill_id），然后从存储过程内部执行INSERT。

更新 II

此错误似乎在5.7.1中已经修复。更改日志如下：

如果将列声明为NOT NULL，则不允许将NULL插入该列或将其更新为NULL。但是，即使有一个设置列为非NULL值的BEFORE INSERT（或BEFORE UPDATE触发器），此约束也会被执行。现在，按照SQL标准，在语句结束时检查约束。

- Kaii