Triggers, Mutating Tables & Column Restrictions

Triggers, Mutating Tables & Column Restrictions

Post by Noah B. Har » Fri, 06 Jun 1997 04:00:00



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

 
 
 

Triggers, Mutating Tables & Column Restrictions

Post by Ian Stevenso » Sat, 07 Jun 1997 04:00:00


Noah,
For a suggested solution, see http://www.westmail.demon.co.uk
Ian


Quote:> I am trying to enforce the following business rule.  
--snip
> Unfortunately, this causes the infamous ORA-4091 "table is mutating"
> error.


 
 
 

1. trigger: mutating table - how to work around?

Hi

I use Oracle 8i  Personal Ed. 8.1.7

I have an employee table with employeeid, name, and managerid (which
reference itself(employeeid))

employeeid    name           managerid

1              tom              3
2              sam             3
3              nick            NULL
4              liz               3

I want to protect from being deleted all rows in the table that is a manager
with existing subordinating employees (like in this case nick and liz must
not be deleted)

I tried as follows in a "before delete" trigger but get the mutating error

create a cursor of the table
compare the employeeid of the record to be deleted with
employee_row.managerid
if find a match then raise_application_error(-20001,'can't del')

If I create a view, then put a "instead of delete" trigger on this view, it
works fine. But is there a way to put a trigger directly on the table in
this case?

Thanks for any help
Nick

2. Delphi 1.02 / netware 4/.dbf - corrupted dbfs'

3. Trigger error - mutating table

4. Btrieve To Paradox

5. table CLASS is mutating, trigger/function may not see it

6. Enterprise Manager "Error"

7. ORA-06512 table is mutating, trigger/function may not see it

8. From form to database.

9. Triggers and mutating tables

10. table is mutating - trigger may not see it

11. Triggers and mutating tables, small example

12. Delete Trigger/Mutating Table Problem

13. Mutating tables, constraints, and triggers