使用MySQL触发器更新客户余额 [英] Using MySQL triggers to update customers balance

问题描述

在了解触发器及其工作原理时，我需要一些帮助.我有3张桌子:

I need some help in understanding triggers and how they work. I have 3 tables:

客户
ID |余额

Customers
Id | Balance

发票
ID |监护人|金额

Invoices
Id | Custid | Amount

付款
ID |客户编号|金额

Payments
Id | CustId | Amount

我有一个插入语句来插入发票:

I have an insert statement to insert the invoices:

$this->db->insert('invoices', array(
            'CustomerId' => $data['customerId'],
            'Description' => $data['Description'],
            'DateCreated' => $data['DateCreated'],
            'Amount' => $data['Amount']
        ));

，并且需要在插入后更新客户余额.同样，在插入或创建付款后.我需要从客户余额中扣除.

and need to update the customers balance after the insert. Similarly, after inserting or creating a payment. I need to deduct from the clients balance.

public function createPayment($data) {
        $this->db->insert('payments', array(
            'CustomerId' => $data['customerid'],
            'DateCreated' => date("Y-m-d H:i:s"),
            'Amount' => $data['amount']
        ));
    } 

在创建这些触发器时将提供任何帮助.

Any assistance would be appreciated in creating these triggers.

推荐答案

您将需要两个触发器-一个用于发票表:

You'll need two triggers - one for the invoice table:

delimiter //
CREATE TRIGGER add_invoice_to_balance AFTER INSERT ON invoices
FOR EACH
ROW
BEGIN
    UPDATE Customers SET balance = balance + NEW.Amount
      WHERE Customers.id = NEW.custid;
END;
//
delimiter;

还有一个支付表:

delimiter //
CREATE TRIGGER add_payment_to_balance AFTER INSERT ON payments
FOR EACH
ROW
BEGIN
    UPDATE Customers SET balance = balance - NEW.Amount
      WHERE Customers.id = NEW.custid;
END;
//
delimiter ;

在这里拨弄

这篇关于使用MySQL触发器更新客户余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！