I have managed to develop one solution using functions and triggers. Has
anyone got a better solution?
Gary
create unique index "compound_bays_unique_index" on compound_bays using btree
(co_id,cr_id,cb_id);
create or replace function compound_rows_range_check() returns trigger as
$proc$
DECLARE BAYNO int4;
BEGIN -- if changing compound or row fail IF NEW.co_id <> OLD.co_id THEN RAISE EXCEPTION 'cannot change compound id';
ENDIF; IF NEW.cr_id <> OLD.cr_id THEN RAISE EXCEPTION 'cannot change row id'; END IF; SELECT cb_id into BAYNO from
compound_bayswhere co_id = NEW.co_id and cr_id = NEW.cr_id and cb_id > NEW.cr_length order by cb_id
desc limit 1; IF found THEN RAISE EXCEPTION 'Cannot remove occupied bays: % > %', BAYNO, NEW.cr_length; END
IF;RETURN NEW;
END;
$proc$ LANGUAGE plpgsql;
CREATE TRIGGER compound_rows_range_check BEFORE UPDATE on compound_rows FOR EACH ROW EXECUTE PROCEDURE
compound_rows_range_check();
create or replace function compound_bays_range_check() returns trigger as
$proc$
DECLARE ROWLENGTH int4;
BEGIN SELECT cr_length into ROWLENGTH from compound_rows where co_id = NEW.co_id and cr_id = NEW.cr_id; IF not
foundTHEN RAISE EXCEPTION 'Compound / Row not found'; END IF; IF NEW.cb_id > ROWLENGTH THEN RAISE EXCEPTION 'row
lengthexceeded: % > %', NEW.cb_id,ROWLENGTH; END IF; RETURN NEW;
END;
$proc$ LANGUAGE plpgsql;
CREATE TRIGGER compound_bays_range_check BEFORE INSERT OR UPDATE on
compound_bays FOR EACH ROW EXECUTE PROCEDURE compound_bays_range_check();
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
