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

Question

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();

Aside: don't quote the language name. It's an identifier, not a value. Use LANGUAGE plpgsql without quotes. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 30, 2014 at 17:59

Vlad Mihalcea · Accepted Answer · 2021-01-09 17:59:26Z

CHECK constraint

The easiest way to achieve this goal is via the CHECK constraint, which is an SQL standard feature.

ALTER TABLE Product
ADD CONSTRAINT verify_expiration_date_check
CHECK (expirationDate < CURRENT_TIMESTAMP)

Trigger function

You could also use a BEFORE INSERT or BEFORE UPDATE trigger, but that's more suitable when the validation rule needs to inspect multiple tables, not just the current on that triggered the check.

Now, if you really want to use a trigger function, this is how it would look like in your case

First, you need to create the trigger function:

CREATE OR REPLACE FUNCTION product_expiration_date()
  RETURNS TRIGGER AS $$
BEGIN
  IF NEW.expirationDate > CURRENT_TIMESTAMP
  THEN
    RAISE EXCEPTION 'Product [id:%] expired!',
    NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Notice that we are throwing an exception using RAISE if the expirationDate is greater than the current timestamp.

Now, you need to pass this trigger function to an INSERT and UPDATE trigger:

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

Although you could use a trigger function, it's much easier to use a CHECK constraint.

Daniel Vérité · Accepted Answer · 2014-03-30 17:48:16Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

CHECK constraint

Trigger function

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

CHECK constraint

Trigger function

Comments

Comments

Related