CODEWITHSUNDEEP
sqlpostgresql
RAFIQ
RAFIQ

Reputation: 1085

Postgres error relation "new" does not exist on insert trigger

Below trigger executes with out any error, but throws 

ERROR:  relation "new" does not exist

on inserting single row into tbl1

CREATE OR REPLACE FUNCTION update_tbl1() 
    RETURNS TRIGGER AS
    $BODY$ DECLARE
DECLARE no_of_rows INT;
BEGIN
    SELECT COUNT(*) as no_of_rows FROM NEW; --error location
         ...
         ...
    COMMIT TRANSACTION
;
END; 
 $BODY$ LANGUAGE plpgsql;
CREATE TRIGGER update_tbl2 AFTER INSERT ON tbl1
     EXECUTE PROCEDURE update_tbl1();

I am unable refer INSERTED, DELETED (SQL SERVER) rows in postgres using NEW and OLD, I appreciate any help.

I need to update a table similar to this

Table-Bill
item  cost  subtotal
itm1  1     1
itm2  5     6
itm3  4     10
itm4  3     13

My input is 'item' and 'cost' column, and trigger needs to update 'subtotal' with sum of all items inserted so far, I am not sure whether to use 'FOR EACH ROW' or for statement and then use cursor please help with solution with good performance

Upvotes: 3

Views: 5867

Answers (1)

RAFIQ
RAFIQ

Reputation: 1085

FOR EACH STATEMENT does not support NEW and OLD usage so only way in current postgres is to go by FOR EACH ROW to access updated/inserted/deleted rows check this post for more information How to use 'for statement' triggers in postgres?

Upvotes: 3

Related Questions