Trigger error when deleting row

Question

When attempting to delete a row, I get the following error:

record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function system_factmetrics_log_change_mv_a_iudt() line 3 at IF

Here is my function:

CREATE OR REPLACE FUNCTION system_factmetrics_log_change_mv_a_iudt() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE plpgsql AS '
BEGIN
  IF NEW IS NULL THEN
    -- must be a DELETE, use OLD
    INSERT INTO system_dailystats_mv_pending_updates (date_id, creative_id, mv_updating, triggering_operation)
      VALUES (OLD.date_id, OLD.creative_id, false, TG_OP);
  ELSE
    INSERT INTO system_dailystats_mv_pending_updates (date_id, creative_id, mv_updating, triggering_operation)
      VALUES (NEW.date_id, NEW.creative_id, false, TG_OP);
  END IF;
  RETURN NULL;
END';

My trigger:

DROP TRIGGER IF EXISTS system_factmetrics_log_change_mv_it ON system_factmetrics;
CREATE TRIGGER system_factmetrics_log_change_mv_it AFTER INSERT OR UPDATE OR DELETE ON system_factmetrics
  FOR EACH ROW EXECUTE PROCEDURE system_factmetrics_log_change_mv_a_iudt();

Shouldn't my IF NEW IS NULL THEN conditional handle deletes adequately? According to the docs NEW should be NULL for DELETE triggers.

What am I missing?

The server is running Postgres 9.2.6.

Greg · Accepted Answer · 2014-09-06 18:02:37Z

1

Maybe something like this would work:

    IF (TG_OP = 'DELETE') THEN
        INSERT INTO system_dailystats_mv_pending_updates
            (date_id, creative_id, mv_updating, triggering_operation)
        VALUES (OLD.date_id, OLD.creative_id, false, TG_OP);
    ELSIF (TG_OP = 'UPDATE' or TG_OP = 'INSERT') THEN
        INSERT INTO system_dailystats_mv_pending_updates
            (date_id, creative_id, mv_updating, triggering_operation)
        VALUES (NEW.date_id, NEW.creative_id, false, TG_OP);
    END IF;
    RETURN NULL;

I don't think NEW is null in your case, I think it just doesn't exist. The docs say it is null for statement level triggers, yours is row level.

answered Sep 6, 2014 at 18:02

Greg

6,7892 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger error when deleting row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related