我有这样的场景：

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

我正在使用MySql 5.0

编辑

在我的场景中,table1保存了账单明细,也就是说,table1有账单和要支付的总金额.客户支付一定数额的未结余额或将支付特定账单.我想做的是.当我没有提供bill_id(这是table1中的主键和table2中的外键)时,我想搜索表1中到期的最旧账单并扣除应付金额,并进一步扣除剩余金额(如果有的话)收单中的下一个账单.我想在数据库层而不是上层执行此操作.因此,当执行插入而没有外键值时,应该通过触发器检索并放置该值,或者直接插入该值.我如何实现这一目标？

使用这里提供的答案,我试过这个：

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 * /

解决方法:

您可以在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;

UPDATE

由于MySQL Bug,这仅在允许列为NULL并且列上没有约束( – >外键)时才有效.原因是MySQL与其他DBMS不同,在执行BEFORE INSERT触发器之前检查约束,并有效地避免执行将纠正要插入的数据的触发器.

在MySQL的行为发生变化之前,唯一的解决方案是使用STORED PROCEDURE而不是普通的INSERT.然后使用应插入的值调用存储过程.在该过程中,完成数据校正(如在这种情况下：选择正确的bill_id),然后从存储过程中执行INSERT.

更新II

这个错误似乎在5.7.1中得到修复.变更日志说：

If a column is declared as NOT NULL, it is not permitted to insert
NULL into the column or update it to NULL. However, this constraint
was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE
trigger) that set the column to a non-NULL value. Now the constraint
is checked at the end of the statement, per the SQL standard.