CODEWITHSUNDEEP
databasepostgresqltriggers
Lucas Trestka
Lucas Trestka

Reputation: 91

NEW inserted item is NULL on insert when using trigger function

I am trying to update a timestamp in a table called asset when an insert is made in another table using the foreign key asset_id... in psuedo code what i mean is:

on insert into table B,

update asset set last_updated = now()

where asset.asset_id = B.asset_id;

Below is the Postgres script i am trying to use to accomplish that:

CREATE OR REPLACE FUNCTION update_asset () RETURNS trigger
LANGUAGE PLPGSQL
AS $plpgsql$
BEGIN
  IF NEW is NULL THEN RAISE EXCEPTION 'entry is null';
  end IF;
  UPDATE asset
  SET last_updated_timestamp = NOW()
  WHERE asset_id = NEW.asset_id;
END;
$plpgsql$;

CREATE TRIGGER table_b_update_trigger
BEFORE  INSERT OR UPDATE ON table_b
EXECUTE PROCEDURE update_asset();

The Problem I am facing is that NEW is always NULL. Does anyone know where i'm going wrong?

Upvotes: 1

Views: 3587

Answers (2)

Fritz
Fritz

Reputation: 472

I had the same problem, and it looks like you cannot compare NEW or OLD against NULL. I replaced the condition IF NEW IS NULL by IF tg_op = 'DELETE' and it worked.

Upvotes: 0

Don R
Don R

Reputation: 623

Use FOR EACH ROW: FOR EACH ROW EXECUTE PROCEDURE update_asset();

NEW is NULL for statement-level triggers.

Upvotes: 7

Related Questions