trigger.sql

CREATE FUNCTION properties_insert_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
      DECLARE
        existing_property properties%ROWTYPE;
      BEGIN
        SELECT * into existing_property from properties where mls_id = NEW.mls_id and ln = NEW.ln;
        IF FOUND THEN
          RAISE EXCEPTION 'A property already exists with that mls_id and ln';
        END IF;
        IF ( NEW.publicly_viewable is TRUE ) THEN
          INSERT INTO properties_publicly_viewable VALUES (NEW.*);
        ELSEIF ( NEW.publicly_viewable is FALSE ) THEN
          INSERT INTO properties_not_publicly_viewable VALUES (NEW.*);
        ELSE
          RAISE EXCEPTION 'Bad publicly viewable value.  Fix the properties_insert_trigger() function!';
        END IF;
        RETURN NULL;
      END;
      $$;