Postgres Trigger After Insert Accessing New

Postgres trigger after insert accessing NEW

From the fine manual:

36.1. Overview of Trigger Behavior
[...]

For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers. Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement.

And from Trigger Procedures:

NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.

Note what it says about row-level triggers and statement-level triggers.

You have a statement-level trigger:

...
FOR EACH STATEMENT
EXECUTE PROCEDURE f_log_datei();

Statement-level triggers are triggered once per statement and a statement can apply to multiple rows so the notion of affected row (which is what NEW and OLD are about) simply doesn't apply.

If you want to use NEW (or OLD) in a trigger then you want the trigger to execute for each affected row and that means you want a row-level trigger:

CREATE TRIGGER log_datei AFTER INSERT OR UPDATE OR DELETE
ON dateien
FOR EACH ROW
EXECUTE PROCEDURE f_log_datei();

I just changed FOR EACH STATEMENT to FOR EACH ROW.

---

Your trigger should also be returning something:

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.

[...]

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.

So you should RETURN NEW; or RETURN NULL; in your trigger. You have an AFTER trigger so it doesn't matter which RETURN you use but I'd go with RETURN NEW;.

PosgreSQL trigger function update after insert

If you want to change the row that was just inserted, don't use UPDATE assign the value to the field or the new record.

To access the column values, use the new record as well.

Something like this:

CREATE OR REPLACE FUNCTION czynsz_clear() 
  RETURNS TRIGGER 
AS $body$ 
BEGIN
  if new.administrative_fees IS NOT NULL 
     AND new.administrative_fees_m2 IS NULL 
     AND new.area IS NOT NULL
     AND new.type_id IN (6,1)
     AND new.administrative_fees > 1 
     AND new.area > 1
  THEN    
    new.administrative_fees_m2 := TRUNC((administrative_fees/ AREA)::INTEGER,2);
  END IF;

  RETURN NEW; -- this needs to be outside of the IF
END;
$body$ LANGUAGE plpgsql;

This assumes the trigger is defined as a row level trigger, e.g.

You also need to make it a BEFORE trigger

create trigger ..
  BEFORE INSERT OR UPDATE on ....
  FOR EACH ROW execute procedure czynsz_clear();

---

The error you got was caused by the fact that your UPDATE statement was not ended with a ;. But even after fixing that, you would have gotten errors, because the column names (in the IF part) can't be referenced like that.

And finally the trigger would have only worked in case the IF conditions were true, because otherwise the trigger would not return anything. So the return new; needs to be outside the IF statement.

How can I create a Postgres 11 trigger function which inserts a new row in table 'b' upon insert or update to table 'a'?

NEW references the inserted or updated row. Therefore NEW. only makes sense with a field identifier.

Also value_1, value_2 and value_quantity have a NOT NULL constraint, which means that you need not test for them.

So you can just drop the whole conditional:

CREATE OR REPLACE FUNCTION the_schema.populate_test_alias()
RETURNS TRIGGER AS
$BODY$
BEGIN
--IF NEW.the_schema.test_values THEN 
INSERT INTO the_schema.test_alias (value_1_copy, value_2_copy, value_quantity_copy)
VALUES (NEW.value_1, NEW.value_2, NEW.value_quantity);
--END IF;
return null;
END; 
$BODY$ LANGUAGE plpgsql;

Postgres 'after insert or update' trigger isn't firing

The INSERT with having a null value for old. was causing me issues when I tried @JGH's suggestion. I ended up with this code which just feels dirty:

  IF( TG_OP = 'INSERT' OR old.STATUS_CODE <> new.STATUS_CODE ) THEN

I still don't understand why the original code wouldn't work for UPDATE statements. The only difference between this and that is the !=. Maybe that's a syntax issue for plpgsql?

Either way, this appears to work from both a SQL window and the application.

PostgreSQL AFTER INSERT trigger, and referencing the inserted row

Not sure why it happens but you could do something along the line of

INSERT INTO C
  SELECT NEW.*, B.*
    FROM B
    WHERE B.key = NEW.key   

Postgresql using Trigger to update column values after inserting values

It is terribly inefficient to update the row after you inserted it. Better is to use a BEFORE trigger that can modify the new row before it is inserted:

CREATE OR REPLACE FUNCTION cal() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN 
   NEW.amount_hkd := NEW.amounts * NEW.currency_conversion_rate;
   RETURN NEW; 
END;$$;

CREATE TRIGGER update_amount 
   BEGORE INSERT ON tableA FOR EACH ROW 
   EXECUTE PROCEDURE cal();

Trigger after insert, update a related model Postgresql

Your trigger function is largely correct. The only problem is that an UPDATE statement cannot use the table.column notation.

From the documentation: Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

CREATE FUNCTION update_post_count() RETURNS TRIGGER AS $updates_user_postCount$
BEGIN
  UPDATE "user"
  SET "post_count" = "post_count" + 1
  WHERE "_id" = NEW.idol_id;
  RETURN NEW;
END;
$updates_user_postCount$ LANGUAGE plpgsql;

PostgreSQL: Trigger INSERT INTO SELECT from other table

You should be using the NEW record in the trigger function to reference the newly inserted data instead of a select, i.e.:

CREATE OR REPLACE FUNCTION triger_function() RETURNS TRIGGER AS
$BODY$
BEGIN
INSERT INTO public.destination_table ( created_at, sale_id, product_id, product_name, url, shop_id, user_id)
VALUES(NEW.created_at,
    NEW.sale_id,
    NEW.product_id,
    NEW.product_name,
    split_part(NEW.url::text, '?'::text, 1),
    NEW.shop_id,
    ((((((((NEW.data #>> '{}'::text[])::jsonb) #>> '{}'::text[])::jsonb) -> 'local_storage'::text) -> 'data'::text) #>> '{}'::text[])::jsonb) ->> 'user_id'::varchar)
       RETURN new;
END;
$BODY$
language plpgsql;

---

Related Topics