The problem is that it is not multi-user-safe.

If, at the same time, 2 users try to add entries with the same name in the different tables, it fails.

User 1__________________User2
----------------_________-------------------
Insert Fred_______________Insert Fred
___in dept__________________in emp
Trigger fires____________Trigger fires
SELECT - no Fred _________SELECT - No Fred
___in emp__________________in dept
Inserts Fred____________Insert Fred
___in dept__________________in emp
Commit__________________Commit

We end up with a Fred in both tables.

Make sense?

As I said before, the only way to guarantee uniqueness is through a unique index. Therefore, as I said, you have to make a single table to hold the name and put a unique index on it. You then need insert/update/delete triggers on the 2 target tables that insert/update/delete the table with unique index. With such a solution, user 2 would have gotten a Unique Key violation when he tried to insert the second Fred.

Make sense?

- Chris