Trigger | how to delete row instead of update based on cell value

Question

Postgresql 10/11.
I need to delete row instead of update in case if target cell value is null.

So I created this trigger function:

CREATE OR REPLACE FUNCTION delete_on_update_related_table() RETURNS trigger
AS $$
    DECLARE
        refColumnName text = TG_ARGV[0];
    BEGIN
        IF TG_NARGS <> 1 THEN
            RAISE EXCEPTION 'Trigger function expects 1 parameters, but got %', TG_NARGS;
        END IF;
        EXECUTE 'DELETE FROM ' || TG_TABLE_NAME || ' WHERE $1 = ''$2'''
        USING refColumnName, OLD.id;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

And a BEFORE UPDATE trigger:

CREATE TRIGGER proper_delete
BEFORE UPDATE OF def_id
ON public.definition_products 
FOR EACH ROW
WHEN (NEW.def_id IS NULL)
EXECUTE PROCEDURE delete_on_update_related_table('def_id');

Table is simple:

id  uuid primary key
def_id  uuid not null

Test:

UPDATE definition_products SET
    def_id = NULL 
WHERE id = 'f47415e8-6b00-4c65-aeb8-cadc15ca5890';
-- rows affected 0

Documentation says:

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row).

Previously, I used a RULE instead of the trigger. But there is no way to use WHERE & RETURNING clause in same rule.

You need an unconditional ON UPDATE DO INSTEAD rule with a RETURNING clause

So, is there a way?

Is the query really supposed to be DELETE FROM definition_products where def_id = OLD.id as you have it? Shouldn't it be where id = OLD.id? — Jeremy, May 15 '19 at 03:43

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

While Jeremy's answer is good, there is still room for improvement.

Problems

You need to be very accurate in the definition of the objective. Your statement:

I need to delete row instead of update in case if target cell value is null.

... does not imply that the column was changed to NULL in the UPDATE at hand. Might have been NULL before, like, before you implemented the trigger. So not:

BEFORE UPDATE OF def_id ON public.definition_products

But just:

BEFORE UPDATE ON public.definition_products

Of course, if the column is defined NOT NULL (as it probably should be), there is no effective difference - except for the noise and an additional point of failure. The manual:

A column-specific trigger (one defined using the UPDATE OFcolumn_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered.

Also, nothing in your question indicates the need for dynamic SQL. (That would be the case if you wanted to reuse the same trigger function for multiple triggers on different tables. And even then it's often better to just create several distinct trigger functions for multiple reason: simpler, faster, less error-prone, easier to read & maintain, ...)

As for "error-prone": your original dynamic statement was just invalid:

EXECUTE 'DELETE FROM ' || TG_TABLE_NAME || ' WHERE $1 = ''$2'''
    USING refColumnName, OLD.id;

Can't pass a column name as value (refColumnName).
Can't put single quotes around $2, which is passed as value and hence needs no quoting.
An unqualified, unquoted TG_TABLE_NAME can go terribly wrong, which is especially critical for a heavy-weight function that deletes rows.

Jeremy's version fixes most, but still features the unqualified TG_TABLE_NAME.

This would be good:

EXECUTE format('DELETE FROM %s WHERE %I = $1', TG_RELID::regclass, refColumnName)   -- refColumnName still unquoted
USING OLD.id;

Or:

EXECUTE format('DELETE FROM %I.%I WHERE %I = $1', TG_TABLE_SCHEMA, TG_TABLE_NAME, refColumnName)
USING OLD.id;

Simpler trigger function:

CREATE OR REPLACE FUNCTION delete_on_update_related_table()
  RETURNS trigger AS
$func$
BEGIN
  DELETE FROM public.definition_products WHERE id = OLD.id;  -- def_id?
  RETURN NULL;
END
$func$  LANGUAGE plpgsql;

Simpler trigger:

CREATE TRIGGER proper_delete
BEFORE UPDATE ON public.definition_products 
FOR EACH ROW
WHEN (NEW.def_id IS NULL)                            -- that's the defining condition!
EXECUTE PROCEDURE delete_on_update_related_table();  -- no parameter

You probably want to use OLD.id, not OLD.def_id. (The row to delete is best defined by it's PK, not by the column changed to NULL.) But that's not entirely clear.

Thanks, using TG_RELID::regclass is indeed much better. – Jeremy Jul 13 '19 at 11:03 — Jeremy, Jul 13 '19 at 11:03

score 1 · Answer 2 · answered May 15 '19 at 13:37

This works for me, with a few small changes:

CREATE OR REPLACE FUNCTION delete_on_update_related_table() RETURNS trigger
AS $$
    DECLARE
        refColumnName text = quote_ident(TG_ARGV[0]);
    BEGIN
        IF TG_NARGS <> 1 THEN RAISE EXCEPTION 'Trigger function expects 1 parameters, but got %', TG_NARGS; END IF;
        EXECUTE format('DELETE FROM %s WHERE %s = %s', quote_ident(TG_TABLE_NAME), refColumnName, quote_literal(OLD.id));
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

-- create trigger
CREATE TRIGGER proper_delete
BEFORE UPDATE OF def_id
ON public.definition_products 
FOR EACH ROW
WHEN (NEW.def_id IS NULL)
EXECUTE PROCEDURE delete_on_update_related_table('id');   --Note id, not def_id

Careful with `TG_TABLE_NAME` and no schema-qualification. – Erwin Brandstetter Jul 13 '19 at 01:58 — Erwin Brandstetter, Jul 13 '19 at 01:58

Trigger | how to delete row instead of update based on cell value

2 Answers2

Problems

Solution

Linked