For my application i need to make a row with a value that can never go below zero. I know about check
but this one can't fail the query if i do for example: update table set special_row = special_row - 100 where special_row = 50
what i want it to do is to just make special_row
be 0 instead o -50 as the query would do.
1 Answer
You can do this with BEFORE INSERT and BEFORE UPDATE triggers. Here is a tutorial.
These triggers can access the row being inserted (or the updated value) in the NEW variable. Do not use an UPDATE statement to change the row! Simply change the NEW variable, have the trigger function RETURN NEW, and the values will be used in the INSERT/UPDATE that fired the trigger. This only works in BEFORE triggers. AFTER INSERT/UPDATE cannot modify values since the operation is already done.
Code would look like this:
CREATE OR REPLACE FUNCTION mytrigger() RETURNS TRIGGER AS $example_table$
BEGIN
IF NEW.yourcolumn < 0 THEN NEW.yourcolumn := 0 END
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;
Then use the CREATE TRIGGER command as mentioned in the tutorial.
update table set special_row = GREATEST(0, special_row - 100) ;