update_triggers_only.sql

--
-- add triggers to populate this values when creating an invoice, paying it, or deleting record
--

DROP TRIGGER IF EXISTS `ccinv_total_ins`;
DELIMITER //
CREATE TRIGGER `ccinv_total_ins` AFTER INSERT ON `cc_invoice_item`
 FOR EACH ROW BEGIN
UPDATE cc_invoice SET total_amount=total_amount+(NEW.price + round(NEW.price*NEW.vat/100,2)),

open_amount=open_amount+(NEW.price + round(NEW.price*NEW.vat/100,2)) WHERE cc_invoice.id=NEW.id_invoice;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `ccinv_total_del`;
DELIMITER //
CREATE TRIGGER `ccinv_total_del` AFTER DELETE ON `cc_invoice_item`
 FOR EACH ROW BEGIN
UPDATE cc_invoice SET total_amount=total_amount-(OLD.price + round(OLD.price*OLD.vat/100,2)),

open_amount=open_amount-(OLD.price + round(OLD.price*OLD.vat/100,2)) WHERE cc_invoice.id=OLD.id_invoice;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `ccinvpay_payment_ins`;
DELIMITER //
CREATE TRIGGER `ccinvpay_payment_ins` AFTER INSERT ON `cc_invoice_payment`
 FOR EACH ROW BEGIN
UPDATE cc_invoice SET open_amount=open_amount-(SELECT payment FROM cc_logpayment WHERE cc_logpayment.id=NEW.id_payment)

WHERE cc_invoice.id=NEW.id_invoice;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `ccinvpay_payment_del`;
DELIMITER //
CREATE TRIGGER `ccinvpay_payment_del` AFTER DELETE ON `cc_invoice_payment`
 FOR EACH ROW BEGIN
UPDATE cc_invoice SET open_amount=open_amount+(SELECT payment FROM cc_logpayment WHERE cc_logpayment.id=OLD.id_payment)

WHERE cc_invoice.id=OLD.id_invoice;
END
//
DELIMITER ;


DROP TRIGGER IF EXISTS `cclogpayment_upd`;
DELIMITER //
CREATE TRIGGER `cclogpayment_upd` AFTER UPDATE ON `cc_logpayment`
 FOR EACH ROW BEGIN
UPDATE cc_invoice SET open_amount=open_amount+(OLD.payment-NEW.payment)
WHERE cc_invoice.id=(SELECT id_invoice FROM cc_invoice_payment WHERE id_payment=OLD.id);
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `ccinvoice_upd`;
DELIMITER //
CREATE TRIGGER `ccinvoice_upd` BEFORE UPDATE ON `cc_invoice`
 FOR EACH ROW BEGIN
   IF (NEW.open_amount = 0) THEN
     SET NEW.paid_status = 1;
   ELSE
     SET NEW.paid_status = 0;
   END IF;
END
//