triggers problem

  • 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


    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 )


    raiserror (' u have entered a dup city',16,1)

    rollback transaction



    if @city not in (select city from city)


    insert into testcity values('888','keyur','6666','7777')


    -- 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


  • 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 )


    if exists (select city from city where city = @city)

  • 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