Trigger for checking a given value before INSERT or UPDATE on PostgreSQL

I have to create a trigger to update a database where there are products. Products have an expiration date and before inserting or updating a product I must check if the expirationDate is superior to the current timestamp. If it is I must do the insert/update regularly (this is what I have problems with). If not I just simply ignore it.

Here’s the code that I have written.

CREATE FUNCTION product_expiration_date()
RETURNS trigger AS $BODY$

BEGIN
IF new.expirationDate > CURRENT_TIMESTAMP THEN
    INSERT INTO Product VALUES (new).*; 
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';

CREATE TRIGGER verify_expiration_date BEFORE INSERT OR UPDATE ON Product 
FOR EACH ROW EXECUTE PROCEDURE product_expiration_date();

Answer

Your trigger function should return NEW when the condition to check is satisfied and you want the INSERT/UPDATE to happen, otherwise NULL to skip the operation, or better, raise an exception with a specific error message.

It must not execute itself the INSERT it was called for, this will be done by the SQL engine with the values in NEW.

Code to skip the operation:

IF new.expirationDate > CURRENT_TIMESTAMP THEN
  RETURN NEW;
ELSE 
  RETURN NULL;
END IF;

Or with an exception:

IF new.expirationDate > CURRENT_TIMESTAMP THEN
  RETURN NEW;
ELSE 
  RAISE EXCEPTION 'Invalid expiration date';
END IF;

See Overview of Trigger Behavior in the doc for more.