如何使用一个INSERT触发器

问题描述：

之前，我有一个这样的场景来分配外键值：如何使用一个INSERT触发器

有两个表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”不能为空* /

什么为当'INSERT'为'table2'没有提供选择从'table1'主键的条件？只是为了拥有有效的关系/外键而随机选择一个呢？换句话说： – Kaii 2012-02-15 07:16:37

：你能更准确地实现你想要实现的目标吗？ – Kaii 2012-02-15 07:42:31

@Kaii看到我的编辑 – 2012-02-15 08:51:00

答

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

（ - >外键）0

UPDATE

因为MySQL Bug的，当列允许为NULL，并且在列没有限制，这只会工作。原因在于，与其他DBMS不同，MySQL在之前检查约束条件，并且有效地避免执行触发器来纠正要插入的数据。

直到MySQL的行为发生变化时，唯一的解决方案是使用STORED PROCEDURE而不是简单的INSERT。然后使用应该插入的值调用存储过程。在这个过程中，数据校正（如在这种情况下：选择正确的bill_id）完成，然后INSERT从存储过程中执行。

UPDATE II

这个错误似乎是固定在5.7.1。Changelog说：

如果一列被声明为NOT NULL，则不允许将 NULL插入到列中或将其更新为NULL。然而，这种约束即使有一个BEFORE INSERT（或BEFORE UPDATE触发器），该列设置为一个非NULL值执行。现在，根据SQL标准，在语句结尾处检查约束条件。

如何使用一个INSERT触发器

相关推荐