Postgresql trigger constraint

Question

I am trying to figure out how to make a trigger constraint which I have never done before. I am using Java with Postgresql.

I have a table which looks like so:

I need two things:

A unique constraint which prevents any duplicate [type_of_charge + time_of_use + meter_register_code + period_of_availability] - this has already been done with a standard unique constraint.
The trigger constraint needs to prevent any 'anytime' row being inserted if there is already a row of the same meter_register_code + period_of_availability.
The trigger constraint needs to stop any row being inserted if there is already an 'anytime' value. e.g. Off-Peak cannot be inserted if there is an anytime value already, but can if there is a 'peak' value.

Please edit your question and show us the create table statement as formatted text please, no screen shots please — user330315
– user330315, Commented May 8, 2018 at 10:21
2. sounds as if that could be done using an exclusion constraint, but I can't really read your screenshot so I'm not 100% sure and 3. sounds as if it can be done using a partial unique index — user330315
– user330315, Commented May 8, 2018 at 10:23
A trigger can never guarantee a constraint unless you are using SERIALIZABLE isolation level. What is wrong with the answer I gave here? — Laurenz Albe
– Laurenz Albe, Commented May 8, 2018 at 10:39

Daryn · Accepted Answer · 2018-05-08 13:04:23Z

I seem to have resolved it with the following:

 CREATE OR REPLACE FUNCTION validate_network_pricing_code() returns trigger as $$
  DECLARE
    tester int;
  BEGIN
  IF NEW.time_of_use = 'ANYTIME'
  THEN tester := (SELECT COUNT(*) FROM network_pricing_category_code WHERE (
        network_pricing_category_code.meter_register_code = NEW.meter_register_code AND
        network_pricing_category_code.period_of_availability = NEW.period_of_availability AND
        network_pricing_category_code.network_pricing_category_id = NEW.network_pricing_category_id AND
        network_pricing_category_code.id != NEW.id  
    ));
    END IF;
    IF NEW.time_of_use IS NOT NULL AND NEW.time_of_use != 'ANYTIME' 
    THEN tester := (SELECT COUNT(*) FROM network_pricing_category_code WHERE (
        network_pricing_category_code.time_of_use = 'ANYTIME' AND
        network_pricing_category_code.meter_register_code = NEW.meter_register_code AND
        network_pricing_category_code.period_of_availability = NEW.period_of_availability AND
        network_pricing_category_code.network_pricing_category_id = NEW.network_pricing_category_id AND
        network_pricing_category_code.id != NEW.id  
    ));
    END IF;
    IF (tester != 0) THEN
      RAISE EXCEPTION 'ANYTIME rates cannot be combined with other types of rates - PEAK, SHOULDER, OFF-PEAK etc ';
    END IF;
    RETURN NEW;
  END;
  $$ language plpgsql;


 CREATE TRIGGER validate_network_pricing_code_trigger BEFORE INSERT OR UPDATE ON network_pricing_category_code
      FOR EACH ROW EXECUTE PROCEDURE validate_network_pricing_code();

By modifying a writeup I found here: When Validation Is Not Enough: PostgreSQL Triggers for Data Integrity

Collectives™ on Stack Overflow

Postgresql trigger constraint

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related