Stored Procedure
-- Procedure name: billing_to_invoice_update
BEGIN
UPDATE accounts.0_debtor_trans SET ov_amount=ov_amount+fee-oldfee WHERE trans_no=encounter AND branch_code=pid;
UPDATE accounts.0_debtor_trans_details SET unit_price=fee/units,quantity=units,qty_done=units WHERE debtor_trans_no=encounter AND stock_id=CONCAT(code_type,'/',code);
-- gl updates
UPDATE accounts.0_gl_trans SET amount=ROUND((-1)*fee) WHERE account=code_type AND memo_=CONCAT(code_type,'/',code) AND last_service_encounter=encounter;
UPDATE accounts.0_gl_trans SET amount=ROUND(fee) WHERE account=1100 AND memo_=CONCAT(code_type,'/',code) AND last_service_encounter=encounter;
END
BEGIN
Trigger That Calls The above code
BEGIN
CALL billing_to_invoice_update(OLD.pid,OLD.encounter,
NEW.units,NEW.fee,OLD.fee,OLD.code_type,OLD.code);
END
The above scripts shows a trigger and a stored procedure named : billing_to_invoice_update. The purpose is to update changes on one table in another table.data to be updated may be a large array of 10 or more rows , but when the rows to be affected is more than one,then the stored procedure repeats the updates on the other table-done by stored procedure. Can somebody help me ensure that each row updates only one row in the corresponding table.
