by I am trying to enforce the following business rule.
Within the contract table, CT having columns date_from and date_thru
no contracts may have overlapping dates.
This is enforced with an insert trigger as follows:
==========================================================
FOR EACH ROW
DECLARE
X INTEGER;
bad_date EXCEPTION;
BEGIN
Select count(*) INTO X from CT_Detail CT
where date_from between ct.date_from and ct.date_thru;
if x>0 then raise bad_date;
END IF;
EXCEPTION
WHEN bad_date THEN
raise_application_error(-20001, 'Date range is inconsistent');
END;
===========================================================
This works fine. However, I want to add a restriction for UPDATE as
well.
Unfortunately, this causes the infamous ORA-4091 "table is mutating"
error.
This makes sense, since the old row I am updating may containg data
which
could conflict with the new data. I tried to get around this by adding
the following
condition to my where clause
and rowid <> ct.rowid;
thinking to restrict the current (old) row from the select. This still
gets ORA-4091.
I've looked into the "copy row before update using trigger, check
restrictions after
update using another trigger" method, however I don't see how to raise
an
exception to the update after the row has been changed, since I need to
'back out'
the update, and send an error that the update failed.
Does anyone have a methodology that works for this type of restriction?
--
Noah B. Hart, Systems Administrator