Re: trigger: mutating table - how to work around?

Hi,
there is a worked example of what to do when you need a trigger on mutating tables in Scott Urman's book "Oracle* PL/SQL Programming" Oracle Press ISBN 0-07-882305-6 in the chapter on Triggers (pp. 334-340 in my edition [1997]).

In summary you need three things:

    a statement level trigger that checks your constraint and raises an exception if needed;

    a row level trigger that remembers the identity of the records being deleted;     and a package to hold these remembered rows.

This is how it works:
the package contains a PL/SQL table to hold the keys for the rows deleted which are added by the row level trigger. Finally using the data recorded in the PL/SQL table, the statement level trigger checks the state of the data and raises an exception if appropriate.

BUT, (it's a big one), in your noddy example what you actually should do is to make use of referential integrity, i.e. make employeeid the primary key and managerid a foreign key to it (without cascade delete).

I suspect that, as a general principle, if you're having to resort to complex trigger arrangements to check constraints you've probably got the data model wrong!

Regards
Phil

Nick wrote:

> 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
Received on Thu Oct 31 2002 - 05:36:55 CST