Oracle PL SQL Trigger - To make data consistent

Question

ORACLE / PL SQL TRIGGER I have a table dealing with Residential Properties called unit_tbl.

The primary composite key is (unit_num, complex_num and owner_num) so many owners can own the same unit in the same complex.

Other columns include num_of_bedrooms (ie 4, 3, 2, 1) and property_type (ie house, duplex, apartment, condo).

Assume the following statement was entered:

INSERT INTO unit_tbl
  (unit_id, complex_id, owner_id, num_beds, property_type) 
VALUES
  (001, 1000, 010, 3, 'apartment');

I'd like to raise an error so that if the same unit_id and complex_id are entered for another owner (of the same property) that if the num_beds does not match the previous entry or if the property type does not match the previous entry.

for instance, an error will be raised if someone were to Insert or update the follows:

INSERT INTO unit_tbl
   (unit_id, complex_id, owner_id, num_beds, property_type) 
VALUES
  (001, 1000, 011, 2, 'apartment');  -- num_beds here does not match the same property previously entered.

I've tried creating a Trigger:

CREATE OR REPLACE TRIGGER unit_consist_check
BEFORE INSERT OR UPDATE ON unit_tbl
FOR EACH ROW
DECLARE
BEGIN
IF :NEW.unit_id = :OLD.unit_id AND :NEW.complex_id=:OLD.complex_id AND ( :NEW.num_beds <> :OLD.num_beds OR :NEW.property_type <> :OLD.property_type) THEN
raise_application_error (-20002, 'nconsistent data on bedroom size or property type.  Please make sure this data is identical to previously entered data for this specific unit_id and complex_id');
END IF;
END;
/

I've also tried DECLARING variables and doing a SELECT INTO variable but that seem to give an error about fetching too many lines.

I'm new to PL / SQL so your help and patience would be greatly appreciated.

Oracle PL SQL Trigger - To make data consistent

Answers (1)

Related Questions