Do you have the foreign key defined as "DELETE RESTRICT"?
"Nick" <aroughguy_at_nsp.toughguy.net> wrote in message
news:A87w9.22346$Sr6.662591_at_ozemail.com.au...
> 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 Fri Nov 01 2002 - 08:29:56 CST
