[Show all top banners]

bhan0001
Replies to this thread:

More by bhan0001
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 Any TSQl experts???????
[VIEWED 3140 TIMES]
SAVE! for ease of future access.
Posted on 12-18-06 9:16 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

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
 
Posted on 12-18-06 10:09 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

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
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 7 days
Recommended Popular Threads Controvertial Threads
TPS Work Permit/How long your took?
मन भित्र को पत्रै पत्र!
Travelling to Nepal - TPS AP- PASSPORT
Does the 180 day auto extension apply for TPS?
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters