Take the 2-minute tour ×

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) 
  (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) 
  (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
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');

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.

share|improve this question
How can there be more than one owner for a unit - are you holding historic data too? More importantly, (a) why aren't you holding the units in one table and ownership in another, which would take care of the integrity; and (b) what happens if someone remodels a unit to add/remove a bedroom? –  Alex Poole May 4 '13 at 9:22
Also OLD and NEW refer to the current row, so your comparisons will only work on UPDATE. What happened with that trigger - did it error, or just not ever raise the exception? –  Alex Poole May 4 '13 at 9:26
Hi Alex, This design is to take into account the possibility that there can be two brothers who own the same unit or brother and sister that can own a single unit... The composite key takes into account the ability for more than one owner_id to own a single unit_id in a complex (complex_id).... owner's ARE held in a separate table and they are a foreign key / part of the composite primary key in the unit_tbl. –  user2349371 May 4 '13 at 9:29
Hi Alex... the trigger is created without error, but upon running it with an insert statement I get errors –  user2349371 May 4 '13 at 9:30
add comment

1 Answer

You are using a bad practice -- triggers -- to compensate for another bad practice --denormalisation.

Add a new table to store the unit separately and join that to an owner table using a unit_owner table, and you will no longer need a trigger.

share|improve this answer
Thanks for your help! –  user2349371 May 4 '13 at 11:18
add comment

Your Answer


