Trigger to update transaction balance

When a new transaction is added with a credit or debit value, the resulting balance is automatically calculated based on the previous transaction balance.

CREATE TABLE public.transactions (
  id UUID NOT NULL,
  "userId" UUID NOT NULL,
  credit NUMERIC,
  debit NUMERIC,
  balance NUMERIC,
  "insertedAt" TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  memo VARCHAR,
  CONSTRAINT transactions_pkey PRIMARY KEY(id)
) 
WITH (oids = false);

CREATE OR REPLACE FUNCTION public."updateBalance" (
)
RETURNS trigger AS
$body$
BEGIN
  IF NEW.credit IS NOT NULL THEN
    UPDATE transactions
    SET balance = b.balance + NEW.credit
    FROM (
      SELECT a.balance
      FROM (
        (
          SELECT t.balance, t."insertedAt"
          FROM transactions t
          WHERE t."userId" = NEW."userId"
          AND t.symbol = NEW.symbol
          AND t.id != NEW.id
          ORDER BY t."insertedAt" DESC
          LIMIT 1
        ) UNION (
          select 0 as balance, null as "insertedAt"
        )
      ) a
      ORDER BY a."insertedAt"
      LIMIT 1      
    ) b
    WHERE id = NEW.id;
  END IF;
  IF NEW.debit IS NOT NULL THEN
    UPDATE transactions
    SET balance = b.balance - NEW.debit
    FROM (
      SELECT a.balance
      FROM (
        (
          SELECT t.balance, t."insertedAt"
          FROM transactions t
          WHERE t."userId" = NEW."userId"
          AND t.symbol = NEW.symbol
          AND t.id != NEW.id
          ORDER BY t."insertedAt" DESC
          LIMIT 1
        ) UNION (
          select 0 as balance, null as "insertedAt"
        )
      ) a
      ORDER BY a."insertedAt"
      LIMIT 1      
    ) b
    WHERE id = NEW.id;
  END IF;
  RETURN null;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER "updateBalance"
  AFTER INSERT OR UPDATE OF credit, debit 
  ON public.transactions FOR EACH ROW 
  EXECUTE PROCEDURE public."updateBalance"();