PostgreSQL: `NEW.<value>` returning `NULL` when `AFTER TRIGGER` is fired

Question

Description of the problem:

Hi everyone, i have this table:

CREATE TABLE COMPORDINE (
    CodProdotto CHAR(5) NOT NULL CHECK(CodProdotto ~* '^[0-9]+$'),
    CodOrdine CHAR(5) CHECK(CodOrdine ~* '^[0-9]+$'),
    Prezzo REAL NOT NULL, CHECK(Prezzo >= 0.0)
    CONSTRAINT OrdineFK FOREIGN KEY(CodOrdine) REFERENCES ORDINE(CodOrdine)
    CONSTRAINT ProdottoFK FOREIGN KEY(CodProdotto) REFERENCES PRODOTTO(CodProdotto)
);

on which I declared a TRIGGER:

CREATE TRIGGER updatePrezzoOrdine
AFTER INSERT ON COMPORDINE
FOR EACH ROW
EXECUTE PROCEDURE updatePrezzoOrdine();

and a TRIGGER FUNCTION:

CREATE OR REPLACE FUNCTION updatePrezzoOrdine()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
DECLARE
  Totale REAL;
BEGIN
  SELECT SUM(Prezzo) INTO Totale
  FROM COMPORDINE AS CO
  WHERE CO.CodOrdine = NEW.CodOrdine
  GROUP BY CodOrdine;
  UPDATE ORDINE SET PrezzoTotale = PrezzoTotale + Totale
  WHERE CodOrdine = NEW.CodOrdine;
  RETURN NEW;
END
$$

Everything works and the trigger just fires fine, but no UPDATE is done on table ORDINE (on line 12).

What I experience (and tried):

I've tried to put some print instructions to see if the query was working:

RAISE NOTICE 'NEW.CodOrdine = %', NEW.CodOrdine;
RAISE NOTICE 'NEW.CodProdotto = %', NEW.CodProdotto;
RAISE NOTICE 'NEW.Prezzo = %', NEW.Prezzo;

but the output returns that new row inserted:

INSERT INTO COMPORDINE VALUES ('12345', '11111', 1.80)

has null values:

NOTICE: NEW.CodOrdine = <NULL>
NOTICE: NEW.CodProdotto = <NULL>
NOTICE: NEW.Prezzo = <NULL>

So the UPDATE "fails" because no row in ORDINE matches NULL values:

CodOrdine	PrezzoTotale	DataAcquisto	CodCliente	CodDipendente
12345	default (0.0)	2020-12-12	00000	99999

Apparently the problem could be related to the inserted row, may it?

What I expect:

After INSERT I would the ORDINE.PrezzoTotale column to be updated with the correct total price of every item in COMPORDINE that matches COMPORDINE.CodOrdine = ORDINE.CodOrdine in this way:

INSERT INTO COMPORDINE VALUES ('12345', '11111', 1.80)

CodOrdine	PrezzoTotale	DataAcquisto	CodCliente	CodDipendente
12345	1.80	2020-12-12	00000	99999

Big thanks to anyone in advance

[EDIT #1]:

As asked, here's the output of \d compordine:

**"public.compordine" table:**

|   Colonna  |     Tipo     | Ordinamento | Pu‗ essere null | Default|
|------------|--------------|-------------|-----------------|--------|
|codprodotto | character(5) |             | not null        |        |
|codordine   | character(5) |             | not null        |        |
|prezzo      | real         |             | not null        |        |

**Indexes**:
    "compordine_codprodotto_key" UNIQUE CONSTRAINT, btree (codprodotto)

**Check constraints**:
    "compordine_codordine_check" CHECK (codordine ~* '^[0-9]+$'::text)
    "compordine_codprodotto_check" CHECK (codprodotto ~* '^[0-9]+$'::text)
    "compordine_prezzo_check" CHECK (prezzo > 0::double precision)

**Referential integrity constraints**:
    "ordinefk" FOREIGN KEY (codordine) REFERENCES ordine(codordine) ON UPDATE CASCADE
    "prodottofk" FOREIGN KEY (codprodotto) REFERENCES prodotto(codprodotto) ON UPDATE CASCADE

**Triggers**:
    updateprezzoordine AFTER INSERT ON compordine FOR EACH STATEMENT EXECUTE FUNCTION updateprezzoordine()

**Disabled triggers**:
    updatescorte BEFORE INSERT ON compordine FOR EACH ROW EXECUTE FUNCTION updatescorte()

[EDIT #2]:

So, I did several other attempts to find a solution (SPOILER ALERT: no solution found); what I did, was to create another function and trigger that simply prints the NEW.<column> value after an INSERT statement on a different table called CLIENTE (my suspect was that all this issue was releated only to COMPORDINE table but I was wrong), here's the code:

CREATE TABLE CLIENTE (
    CodCliente CHAR(5) PRIMARY KEY, CHECK(CodCliente ~* '^[0-9]+$'),
    Nome VARCHAR(255) NOT NULL, CHECK(Nome ~* '^[A-Za-z ]+$'),
    Cognome VARCHAR(255) NOT NULL, CHECK(Cognome ~* '^[A-Za-z ]+$'),
    CodiceFiscale CHAR(16) NOT NULL, CHECK(CodiceFiscale ~* '^[A-Z]{6}\d{2}[A-Z]\d{2}[A-Z]\d{3}[A-Z]$'),
    Indirizzo VARCHAR(300) NOT NULL,
    Email VARCHAR(255) NOT NULL, CHECK(Email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-za-z]+$'),
    UNIQUE(CodiceFiscale, Email)
);

CREATE TRIGGER print
BEFORE INSERT ON Cliente
EXECUTE PROCEDURE print();

CREATE FUNCTION print()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    RAISE NOTICE 'CodCliente = %', NEW.CodCliente;
    RETURN NEW;
END
$$

INSERT INTO CLIENTE VALUES ('34567', 'Gianfranco', 'Rana', 'GNFRNA87F24E123A', 'Via Delle Rane 23', 'franco.rane@ravioli.it')

Output returns:

NEW.CodCliente = <NULL>

While it should be:

NEW.CodCliente = <34567>

I simply can't understand why, shouldn't NEW in an AFTER INSERT TRIGGER returns the value this way? :

Event	OLD	NEW
INSERT	NULL	New Record
DELETE	Old Record	NULL
UPDATE	Original Record	Updated Record

Answer 1

Had the same issue. For me the issue was the way I created the trigger:

create trigger pending_balance_add after insert on pending_balance 
execute function update_pending_balance();

I forgot to tell that "for each row" and perhaps "procedure" instead of function:

create trigger pending_balance_add before insert on pending_balance 
for each row 
execute procedure update_pending_balance();

Now inside of update_pending_balance I have correct new.amount, etc.

Regarding your issue, it shows that

Triggers: updateprezzoordine AFTER INSERT ON compordine FOR EACH STATEMENT EXECUTE FUNCTION updateprezzoordine()

Disabled triggers: updatescorte BEFORE INSERT ON compordine FOR EACH ROW EXECUTE FUNCTION updatescorte()

When trigger is executed for statement it has NEW.FIELD values as NULL

Answer 2

PostgreSQL Can't change values in AFTER trigger. Change the trigger to BEFORE. Does not matter if you change the variable record NEW, the data was already changed in the table record. AFTER triggers ignore NEW changes. You can read and write but only read matters.