MySQL触发器失败，但联合工作怎么了？

lzfw57am 于 2022-12-03 发布在 Mysql

关注(0)|答案(1)|浏览(118)

所以这是发票表整洁和跨度工程罚款

create table invoice(
invoice_id DECIMAL(3),
invoice_date DATE,
due_date DATE,
overdue_fee DECIMAL(10,2),
amt_due_left decimal(12,2),
PRIMARY KEY(invoice_id));

INSERT INTO invoice VALUES 
(1,'2020-11-02','2020-11-05',15,120.24),
(2,'2020-11-02','2020-11-05',35,200.00),
(3,'2020-11-02','2020-11-05',150,1300.00),
(4,'2020-11-02','2020-11-05',120,1200.00);

所以这是支付表整洁和跨度工程罚款

insert into payments values
(1,1,"credit_card",120.24,'2020-11-03' ),
(2,2,"cash",200,'2020-11-03' ),
(3,3,"debit",1200.00,'2020-11-03' ),
(4,4,"cash",1200.00,'2020-11-03' );

create table payments(
payment_id int, 
invoice_id decimal(3),
payment_type varchar(40),
amnt_recived decimal(12,2),
payment_date Date,
primary key (payment_id),
CONSTRAINT fk_has_invoice_id
FOREIGN KEY(invoice_id)REFERENCES invoice(invoice_id));

有趣的是这是工会

UPDATE invoice
    INNER JOIN
    payments ON invoice.invoice_id = payments.invoice_id
    set
    amt_due_left = amt_due_left - amnt_recived
    where 
    invoice.invoice_id = payments.invoice_id AND amt_due_left > 0;

这个工会剂量正是什么是它的设计和结果是这为发票表
'1','2020-11-02','2020-11-05','15.00','0.00'
'2','2020-11-02','2020-11-05','35.00','0.00'
'3','2020-11-02','2020-11-05','150.00','100.00'
'4','2020-11-02','2020-11-05','120.00','0.00'
现在，这是已建立的触发器，我删除了到期金额left〉0，因此它显示了真实的结果

DELIMITER $$

CREATE TRIGGER trigger_rmv_payment after insert ON payments FOR EACH ROW
BEGIN
   UPDATE invoice
    inner JOIN
    payments ON invoice.invoice_id = payments.invoice_id
    set
    amt_due_left = amt_due_left - amnt_recived
    where 
    invoice.invoice_id = payments.invoice_id ;
END$$    

DELIMITER ;

这些是触发器的结果
'1','2020-11-02','2020-11-05','15.00','-360.72'
'2','2020-11-02','2020-11-05','35.00','-400.00'
'3','2020-11-02','2020-11-05','150.00','-1100.00'
'4','2020-11-02','2020-11-05','120.00','0.00'
我希望这两个是一样的，我已经经历了数百次迭代，它总是抛出这个错误，我已经使用了NEW。和变化，仍然没有什么。我只是想联盟做同样的事情作为触发器有帮助吗？

mysql

来源：https://stackoverflow.com/questions/74645301/mysql-trigger-fails-but-union-works-whats-up

1条答案

按热度按时间

ubof19bj1#

由于您为插入到payments中的每一行运行触发器，因此不需要在触发器中额外引用payments表，只需在语句中引用NEW.column就足够了，例如：

CREATE TRIGGER trigger_rmv_payment after insert ON payments FOR EACH ROW
BEGIN
    UPDATE  invoice
    SET     amt_due_left = amt_due_left - NEW.amnt_recived
    WHERE   invoice.invoice_id = NEW.invoice_id;
END

不过，我将重申我在评论中说过的话，存储可以计算的值通常不是一个好主意，除非您有很好的理由，比如在需要时运行计算需要花费太长时间--但即使这样，我也会认为这就是OLAP环境的作用，您可以保持OLTP环境干净。但你没有考虑更新或删除。你覆盖了现有的数据，所以一旦付款，原始发票金额就永远丢失了。触发器也可以被禁用，一旦你开始与这种方法不同步，你就有了一个对账的噩梦。我真的，真的，我强烈建议您使用视图或类似工具来计算到期金额，因为它只依赖于底层数据，所以到期金额不会与发票/付款表中的内容不同步：

CREATE VIEW InvoicePayment
AS
SELECT  i.invoice_id,
        i.invoice_date,
        i.due_date,
        i.amt_due_left AS OriginalAmount,
        p.amount AS AmountPaid,
        i.amt_due_left - p.amount AS amt_due_left
FROM    invoice AS i
        LEFT JOIN 
        (  SELECT invoice_id, SUM(amnt_recived) AS amount
           FROM payments
           GROUP BY invoice_id
        ) AS p
          ON p.invoice_id = i.invoice_id

赞(0）回复(0）举报 2022-12-03

我来回答

MySQL触发器失败，但联合工作怎么了？

1条答案

相关问题

热门标签

最新问答