How do i called funtion in the trigger? I mean, i have 2 function 1. that finds the dno from the dept table when dname is given and 2. checks the uniqueness of eno no in emp table.
Now i need to write a trigger called addemp. when new emp is inserted it checks the uniqueness of empno in emp table and checks dname to see if it is in dept table. If it exist then it gets the value of dno to be use in emp table.
This is how i wrote but don't know how to pass value to funtion in the trigger. Any help will be appreciated. Thanks in advance.
Create Trigger trig_emp_add on emp
Instead of insert
as
Begin
declare @emp_exist integer
declare @dept_exist integer
declare @dept_no integer

exec check_eno (INSERTED.eno) -- This is where i have the problem????
if exists = 1
insert into auditemp (eno,ename,sal,deptno)
values (inserted.eno,inserted.ename,inserted.sal,inserted.deptno)
else
exec check_dno(inserted.deptno)
end

Hi bhan0001,
I think if u take little different approach it will be easier. If you need to find uniqueness on emp table just join emp table with inserted table.

1.select @empno = empno from emp as e inner join inserted as i on e.empno = i.empno

if you don't get value @empno you should consider the inserted empno is unique.
set @flag1 = true

for second step just find from inserted table the deptno

2.select @deptno = deptno from inserted as i inner join dept as d on i.deptno = d.deptno
if you get value for @deptno you should consider the deptno is exists
set @flag2 = true

if both flag1 and flag2 true then you can write insert statement.

Hope you understand. Thanks a lot