how do i execute multiple queries inside a procedure?

The task does not need in “new balance is above 0” checking – it can be easily performed by according column CHECK constraint. Then simply update both tables in a transaction – if the balance should fall below zero then constraint violation occures, transaction fails and rolled back.

See DEMO fiddle

Pay attention – the query which may fail due to constraint violation is the most upper. If you swap UPDATE queries then the balance for acceptor will be altered errorneously. In such case you must declare according handler and rollback the transaction if it fires.

See DEMO fiddle

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top