This is my database schema, related to the problem.
This is one of the triggers:
CREATE OR REPLACE FUNCTION "public"."update_balance_bet"() RETURNS "pg_catalog"."trigger" AS $ BODY$ DECLARE currentBalance INTEGER; BEGIN currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId"); UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance); RETURN NEW; END $ BODY$ CREATE TRIGGER bet_trigger AFTER INSERT ON "public"."Bets" FOR EACH ROW EXECUTE FUNCTION update_balance_bet();
Basically from my app I insert records into table Bets, Bonuses, Withdrawals and Deposits, then the rest of the work is done by triggers. My questions is whether the mechanism I used will guarantee consistency in all cases, or should I use raw locking? Do I right in assumption that inserting into Bets table is one transaction along with trigger function updating balance, so in case something goes wrong with insertion into Bets, balance wont be updated OR if something wrong goes with balance updating, new record wont be inserted into Bets table?