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 Received on Thu Oct 31 2002 - 02:40:53 CST