locked
here is trigger if exist then update else inser but not working properly for bulk update(10 or 15 rows) RRS feed

  • Question

  • Hi

    i have a trigger as follows

    ALTER

     

    TRIGGER [ table_detail_insert_update]

    ON

     

      table_detail]

    after

     

    UPDATE,INSERT

     

    IF

     

    EXISTS( SELECT * FROM table_detail WHERE ID =@old_player_id)

     

    BEGIN

     

    UPDATE espd

     

    END

     

     

     

    IF NOT EXISTS( SELECT * FROM [espn_sportz_trigger].dbo.sportz_player_detail WHERE ID =@new_player_id)

     

     

    BEGIN

     

    INSERT INTO table_detail

    END

    end

    its working fine when i will insert or update only one row even work fine when i make bulk insert (10-15 ) but it will not work fine when i update 10 rows .where 2 row are already present and 8 shoul be inserted it will gves the error as follows

    Violation of PRIMARY KEY constraint 'PK_table_detail'. Cannot insert duplicate key in object 'table_detail'

     

    So please tell me how to achive it

     

    AS

    BEGIN

    Wednesday, December 1, 2010 7:11 AM

Answers

  • i have use it

    DECLARE

     

     

    SELECT

    @old_player_id INT,@new_player_id INT

     

     

    SELECT

     

    @new_player_id=id FROM Inserted

     

    then the above code

     

    but thank for your reply

     

    @old_player_id=id FROM deleted


    You have to process one row at a time using deleted/inserted table.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by Tom Li - MSFT Sunday, December 12, 2010 9:22 AM
    Wednesday, December 1, 2010 7:54 AM

All replies

  • There is a logical flaw here..
    Trigger will fire only once for each update/insert statement. You need to use inserted/deleted virtual table in the logic.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, December 1, 2010 7:27 AM
  • i have use it

     

    DECLARE

     

    @old_player_id INT,@new_player_id INT

    SELECT

     

    @old_player_id=id FROM deleted

    SELECT

     

    @new_player_id=id FROM Inserted

     

    then the above code

     

    but thank for your reply

     

    Wednesday, December 1, 2010 7:38 AM
  • i have use it

    DECLARE

     

     

    SELECT

    @old_player_id INT,@new_player_id INT

     

     

    SELECT

     

    @new_player_id=id FROM Inserted

     

    then the above code

     

    but thank for your reply

     

    @old_player_id=id FROM deleted


    You have to process one row at a time using deleted/inserted table.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by Tom Li - MSFT Sunday, December 12, 2010 9:22 AM
    Wednesday, December 1, 2010 7:54 AM