Trigger problem

Can anyone give me a reason why, in de definition of a trigger, there are restrictions on letting the triggered SQL statement act on the triggering table ? These restrictions prevent you from using triggers to enforce referential integrity within ONE table by means of cascading deletes. Consider e.g. a table EMPLOYEE (emp_no, mgr_no), created as follows : create table employee (emp_no smallint primary key, mgr_no smallint references employee); Then the following trigger can be created but results in an error (-747) when fired : create trigger del_employee delete on employee referencing old as erased for each row (delete from employee where mgr_no = erased.emp_no) The error message is : "-747 Table or column matches object referenced in triggering statement. This error is returned when a triggered SQL statement acts on the triggering table, or when both statements are updates and the column being updated in the triggered action is the same as the column being updated by the triggering statement." However the following 2 triggers work perfectly : create trigger del_employee delete on employee referencing old as erased for each row (update employee set mgr_no = erased.mgr_no where mgr_no = erased.emp_no) (the above trigger replaces the manager by the manager's manager) create trigger upd_employee update of emp_no on employee referencing old as pre new as post for each row (update employee set mgr_no = post.emp_no where mgr_no = pre.emp_no) I also wonder if there is a way to circumvent the problem with the first trigger. -- ----------------------------------------------------------- Ria Vandenberghe Ria.Vandenberghe@rug.ac.be UNIVERSITY OF GHENT (BELGIUM) - Computer Science Laboratory Technologiepark-Zwijnaarde 9, B-9052 ZWIJNAARDE, BELGIUM tel: +32/9/264.55.10 fax: +32/9/264.58.42