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 ;