0

I'm trying to create a trigger that prevents a column to be updated as follows:

If column value is "A", it cannot be updated to "B", and if column value is "B", it cannot be updated to "A".

CREATE OR REPLACE FUNCTION cancelled_order()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$BEGIN
    IF OLD.status = 'Cancelled' THEN NEW.status != 'Accomplished';
    END IF;
    IF OLD.status = 'Accomplished' THEN NEW.status != 'Cancelled';
    END IF;
    RETURN NEW;
END;
$function$;

CREATE TRIGGER No_cancelled_acomplished
    BEFORE UPDATE OF status
    ON clinical.tb_orders
    EXECUTE PROCEDURE cancelled_order();

The error that comes out is:

Sintaxis error: != 

Any idea, why?

2
  • Hi and welcome to the forum! Please construct a fiddle on dbfiddle.uk and provide some sample data and your desired result - put any data from the fiddle here also (use the edit question link). – Vérace May 6 at 10:34
  • 1
    The crucial question is what to do in case of an illegal attempt: Just prevent changing the column value? Reset the column to a default value? Cancel the whole update silently? Raise an exception (to cancel the whole transaction)? Please be specific. – Erwin Brandstetter May 10 at 1:31
0

Based on what you want to accomplish logically, shouldn't:

IF OLD.status = 'Cancelled' THEN NEW.status != 'Accomplished';
END IF;
IF OLD.status = 'Accomplished' THEN NEW.status != 'Cancelled';
END IF;

actually say something like this?

IF OLD.status = 'Cancelled' AND NEW.status = 'Accomplished' THEN RETURN OLD; -- Set A = A when trying to set it to B
END IF;
IF OLD.status = 'Accomplished' AND NEW.status = 'Cancelled' THEN RETURN OLD; -- Set B = B when trying to set it to A
END IF;

My syntax might be slightly off for PostgreSQL, but my point is isn't this the kind of logical expression you're looking for? (You shouldn't be doing a boolean comparison in the THEN clause, which I think is why it's mad about the !=.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.