August 29, 2001 at 11:54 pm
hello there,
i have written a trigger for the city table. whenever a user enters a value in it it will check whether the city is new or old, if its new then it should allow it to enter the new value, but its not working here is the code :
create trigger keyurtrig11 on testcity
for
insert as
declare @city varchar(25)
select @city = city
from testcity
--insert into testcity values('888','keyur','6666','7777')
if @city in (select city from city )
begin
raiserror (' u have entered a dup city',16,1)
rollback transaction
end
/*else
if @city not in (select city from city)
begin
insert into testcity values('888','keyur','6666','7777')
end*/
-- insert into testcity values('888','sam','6666','7777')
the problem is whichever city i enter its showing u entered a dup city
thanks in advance
keyur
August 30, 2001 at 4:54 am
this query is the problem
select @city = city
from testcity --
This should be
select @city = city
from inserted
also it would be better if you replaced this
if @city in (select city from city )
with
if exists (select city from city where city = @city)
August 30, 2001 at 4:56 am
oh and you would also need to loop thru all records in inserted . currently the trigger is geared to manage single records only.. read up on triggers in BOL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply