Triggers, Mutating Tables & Column Restrictions

From: Noah B. Hart <noah_at_lipman.com>
Date: 1997/06/05
Message-ID: <01bc71db$37c40120$875395c0@redwood.lipman.com>#1/1

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
noah_at_lipman.com  --  www.lipman.com/noah

Received on Thu Jun 05 1997 - 00:00:00 CDT