CODEWITHSUNDEEP
postgresqlpostgresql-9.2triggers
erikcw
erikcw

Reputation: 11057

Trigger error when deleting row

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.

Upvotes: 1

Views: 905

Answers (1)

Greg
Greg

Reputation: 6759

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.

Upvotes: 1

Related Questions