使用 MySQL 触发器更新客户余额
我需要一些帮助来理解触发器及其工作原理.我有 3 张桌子:
I need some help in understanding triggers and how they work. I have 3 tables:
客户
身份证 |余额
Customers
Id | Balance
发票
身份证 |客户 |金额
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 ;
在这里小提琴
相关文章