Hi

I have created following tables

emp

empname primary key

dept

deptname primary key

I have written two triggers one for emp table and the other for dept table I feel you dont want deptname to have the same values which are there in empname and vice versa I have tested these triggers only once before you implement on ur production please test the code rigorously and change it according to ur environment

Employee table trigger

create or replace trigger emp_trigger
before insert or update on emp for
each row
declare
deptname char(20);
begin
select deptname into deptname from dept where
dept.deptname = :new.empname;
if sql%found
then
raise_application_error(-20000,'Empname cannot be the same as deptname');
else
null;
end if;
exception
when no_data_found
then
null;
end;


Dept table trigger

create or replace trigger dept_trigger
before insert or update on dept for
each row
declare
empname char(20);
begin
select empname into empname from emp where
emp.empname = :new.deptname;
if sql%found
then
raise_application_error(-20000,'Deptname cannot be the same as Empname');
else
null;
end if;
exception
when no_data_found
then
null;
end;


I Hope this is what you are looking for and it helps

Regards