Postgresql - Function to prevent UPDATE from occurring

Question

Using PostgreSQL 11.6. I want to prevent an UPDATE to occur on a given column, if a different column data meets certain criteria. I figure the best way is via an Event Trigger, before update.

Goal: if column 'sysdescr' = 'no_response' then do NOT update column 'snmp_community'.

What I tried in my function below is to skip/pass on a given update when that criteria is met. But it is preventing any updates, even when the criteria doesn't match.

CREATE OR REPLACE FUNCTION public.validate_sysdescr()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
    IF NEW.sysdescr = 'no_response' THEN
        RETURN NULL;
    ELSE
        RETURN NEW;
    END IF;
END;
$function$;

Note: I was thinking using some type of 'skip' action may be best, to make the function more re-usable. But if I need to call out the specific column to not update (snmp_community) that's fine.

What exactly do you mean by "preventing any updates" - even those where sysdescr <> 'no_response', or those with sysdescr = 'no_response' but where you updated some column other than snmp_community? — Bergi
– Bergi, Commented Nov 27, 2019 at 1:07
Rodrigo below provided a working solution for me. To answer your question if the value of column sysdesc is 'no_response' dont let a query update the column 'snmp_community' ... otherwise permit the update to the 'snmp_community' column. I am querying SNMP against network devices, but if the string was wrong I send 'no_response' to SQL. If that has happened, don't record the snmp_string attempted - since that would be mis-leading. — refriedjello
– refriedjello, Commented Nov 27, 2019 at 2:20

Rodrigo · Accepted Answer · 2019-11-27 01:10:18Z

1

Change the procedure to:

CREATE OR REPLACE FUNCTION public.validate_sysdescr()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
    IF NEW.sysdescr = 'no_response' THEN
        NEW.snmp_community = OLD.snmp_community ;
    END IF;
    RETURN NEW;
END;
$function$;

And associate it to a common on update trigger:

CREATE TRIGGER validate_sysdescr_trg  BEFORE UPDATE ON <YOUR_TABLE>
    FOR EACH ROW
    EXECUTE PROCEDURE public.validate_sysdescr();

answered Nov 27, 2019 at 1:10

Rodrigo

2,5132 gold badges19 silver badges24 bronze badges

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

1 Comment

refriedjello Over a year ago

This works, thanks! So don't try to prevent/block the update from happening, but instead intercept that query, and reset that col value it back to what it was before.

Collectives™ on Stack Overflow

Postgresql - Function to prevent UPDATE from occurring

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related