How to automatically change the total column when adding a new line?

Question

PostgreSQL

I wrote a trigger, but something doesn't work:

CREATE OR REPLACE FUNCTION autocuontfunc() RETURNS TRIGGER AS $big_bank$
   BEGIN
      INSERT INTO big_bank(total) VALUES (NEW.total  = percent * score);
      RETURN NEW;
   END;
$big_bank$ LANGUAGE plpgsql;

CREATE TRIGGER auto_cuont BEFORE DELETE ON big_bank FOR EACH ROW EXECUTE PROCEDURE autocuontfunc();

Here is the structure of the table:

CREATE TABLE big_bank(
    id SERIAL NOT NULL PRIMARY KEY,
    fio TEXT NOT NULL,
    percent numeric DEFAULT 0,
    score NUMERIC DEFAULT 0,
    total NUMERIC DEFAULT 0
);

When trying to add a new user or client of the "bank", the total column should be automatically calculated (according to my plan), but 0 still hangs there. What is the error?

Answer 1

when adding a new line

Don't find anything strange?
However, my crystal ball shows what you need at all

CREATE TABLE big_bank(
    id SERIAL NOT NULL PRIMARY KEY,
    fio TEXT NOT NULL,
    percent numeric DEFAULT 0,
    score NUMERIC DEFAULT 0,
    total NUMERIC GENERATED ALWAYS AS (percent * score) STORED
);

Well, or before postgresql 12 emulate yes, with a trigger, but like this:

CREATE OR REPLACE FUNCTION autocuontfunc() RETURNS TRIGGER AS $big_bank$
   BEGIN
      NEW.total = NEW.percent * NEW.score;
      RETURN NEW;
   END;
$big_bank$ LANGUAGE plpgsql;

CREATE TRIGGER auto_cuont BEFORE INSERT OR UPDATE ON big_bank FOR EACH ROW EXECUTE PROCEDURE autocuontfunc();