Oracle PL SQL Trigger - To make data consistent -

oracle / pl sql trigger have table dealing residential properties called unit_tbl.

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

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

assume following statement entered:

insert unit_tbl   (unit_id, complex_id, owner_id, num_beds, property_type)  values   (001, 1000, 010, 3, 'apartment'); 

i'd raise error if same unit_id , complex_id entered owner (of same property) if num_beds not match previous entry or if property type not match previous entry.

for instance, error raised if insert or update follows:

insert unit_tbl    (unit_id, complex_id, owner_id, num_beds, property_type)  values   (001, 1000, 011, 2, 'apartment');  -- num_beds here not match same property entered. 

i've tried creating trigger:

create or replace trigger unit_consist_check before insert or update on unit_tbl each row declare begin if :new.unit_id = :old.unit_id , :new.complex_id=:old.complex_id , ( :new.num_beds <> :old.num_beds or :new.property_type <> :old.property_type) raise_application_error (-20002, 'nconsistent data on bedroom size or property type.  please make sure data identical entered data specific unit_id , complex_id'); end if; end; / 

i've tried declaring variables , doing select variable seem give error fetching many lines.

i'm new pl / sql , patience appreciated.

you using bad practice -- triggers -- compensate bad practice --denormalisation.

add new table store unit separately , join owner table using unit_owner table, , no longer need trigger.