mysql 触发器 分行,使用MySQL触发器更新客户余额

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

Customers

Id | Balance

Invoices

Id | Custid | Amount

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;

And one for the payment table:

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 ;