0

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.

Edited by pritaeas: Added markdown.

2
Contributors
1
Reply
9
Views
4 Years
Discussion Span
Last Post by SQLpower
0

You might wanna set exception handlers for your update statements, in case one of them fail.

And for "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.

You will have to use a "sum" and store it into a variable and fire up an IF statement.

Can somebody help me ensure that each row updates only one row in the corresponding table.

That bit is a little ambigious. If you want to check the data row by row, you'll have to use a cursor.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.