Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: multiple insert call for a table having insert trigger

    Hi

    I am trying to use multiple insert for a table T1 to add multiple rows.

    Ti has trigger for insert to add or update multiple rows in Table T2.

    When I provide multiple insert SQL then only first insert works while rest insert statements does not work

    Anybody have any idea about why only one insert works for T1

    Thanks

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Looks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

    Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO

    Cheers

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    Thanks for reply me back..
    I am using cursor to call multiple insert inside that curser.. here is the code..

    OPEN DestinationIDList
    FETCH NEXT FROM DestinationIDList INTO @DestinationID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    INSERT INTO Table(ID, DestinationID) VALUES(@ID, @DestinationID)

    FETCH NEXT FROM DestinationIDList INTO @DestinationID
    END
    CLOSE DestinationIDList
    DEALLOCATE DestinationIDList

    What should I have to do so that trigger fire for each insert ?

    Thanks

    Originally posted by aldo_2003
    Looks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

    Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO
    INSERT INTO T1 ---- ETC
    GO

    Cheers

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Cool

    What you could do is to write the Insert as a stored procedure.

    Then call this proc from your cursor passing the variables that you have defined in your cursor


    something like :

    create proc Insert_T1
    (@id as int , @DestinationID as int)
    as

    INSERT INTO T1(ID, DestinationID) VALUES(@ID, @DestinationID)
    go

    All you do call this proc in your cursor land this should make your trigger fire for every insert you perform

    Give it a go and see if it works

    Cheers

  5. #5
    Join Date
    Mar 2004
    Posts
    6
    No Luck :-(

  6. #6
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Can you post the code for your trigger

    Cheers

  7. #7
    Join Date
    Mar 2004
    Posts
    6
    Here is the trigger's code

    CREATE TRIGGER TR_TD
    ON dbo.TRHistory FOR INSERT

    AS

    DECLARE @ID int
    DECLARE @DSTID int
    DECLARE @RT decimal (18,4)
    DECLARE @Time datetime
    DECLARE @GTID int
    DECLARE @GTExist int
    DECLARE @intErrorCode INT
    SET @GTID = 0
    SET @GTExist = 0
    SET @ID = 0

    -- CHECK ID' S VALIDITY

    SELECT @ID = i.ID, @DSTID= i.DSTID,
    @RT = i.RT, @Time = i.Time
    FROM Inserted i
    INNER JOIN ITSPS ON ITSPS.ID = i.ID

    IF @ID <> 0

    BEGIN

    -- FIND OUT NO OF GTS FOR SPECIFIC DSTS TO UPDATE FOR CORRESPONDING GT
    DECLARE GTList CURSOR FOR
    -- SELECT GTS FOR DSTID
    SELECT GTs.GTID
    FROM GTs INNER JOIN
    GTDSTs ON GTs.GTID = GTDSTs.GTID INNER JOIN
    ITSPs ON GTs.ID = ITSPs.ID
    Where GTs.ID = @ID AND DSTID = @DSTID

    OPEN GTList

    FETCH NEXT FROM GTList INTO @GTID

    -- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
    IF (@GTID = 0)
    BEGIN
    SELECT @intErrorCode = 1
    CLOSE GTList
    DEALLOCATE GTList
    GOTO PROBLEM

    END
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- CHECK WIEHTER GT ENTERY EXISTS IN TR TABLE
    -- IF SO THEN UPDATE ELSE INTER NEW FOR GT

    SELECT @GTExist = GTid
    FROM TR
    WHERE GTid = @GTid AND DSTID = @DSTID

    IF @GTExist > 0

    UPDATE TR
    SET RT = @RT,
    Time = getdate()

    WHERE GTID = @GTid


    ELSE

    INSERT INTO TR (GTID, DSTID, RT)
    VALUES (@GTID,@DSTID,@RT)
    -- IN CASE FOR ANY EXCEPTION GO TO PROBLEM PARA AND CLOSE & DEALLOCATE CURSOR
    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0)
    BEGIN
    CLOSE GTList
    DEALLOCATE GTList
    GOTO PROBLEM
    END
    FETCH NEXT FROM GTList INTO @GTID


    END
    CLOSE GTList
    DEALLOCATE GTList
    COMMIT TRAN
    END
    ELSE
    -- INVALID ID
    BEGIN
    RAISERROR (' Invalid ID ', 16, 1)
    ROLLBACK TRAN
    END


    PROBLEM:
    IF (@intErrorCode <> 0)
    BEGIN
    IF (@intErrorCode = 1)
    RAISERROR (' Insert is rejected due to invalid GT DST info', 16, 1)
    ELSE
    RAISERROR ('Error occured to udated info for tr' s. Please contact administrator ', @intErrorCode, 1)
    ROLLBACK TRAN


    END

  8. #8
    Join Date
    Mar 2004
    Posts
    6
    Hi aldo_2003

    I am Waiting.. Only problem is that if i use multiple insert with GO then multiple insert works.. but when i call is in cursor for multiple then it add only first insert..

  9. #9
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Just got back from lunch mate

    Am goiung to try a couple if things and get back to you

    Cheers

  10. #10
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Had a quick look at the trigger

    Don't think it is the GO statement as I managed to get a test trigger to fire twice without the GO.

    I'm not sure why your trigger is only firing once.

    What you might want to do is to try and reduce the complexity of the code within the trigger by putting that logic within the stored proc and calling this from your trigger.

    You have cursors within cursors when you take the trigger into account and this may be causing you issues that you are not aware of.

    Get the trigger to fire properly without any logic in it i.e

    Get the first cursor inserting into your table and the trigger firing a simple insert into a test table. Once you have that then implement your trigger logic in a stored proc and call that from the trigger.

    I hope this helps, let me know how you get on.

    Cheers

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well there are just so many things...


    first you don't need a cursor...collapse the cursor and the insert in to 1...

    second a cursor in a trigger can't be a good idea performance wise....but like I said collapse them

    third

    -- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
    IF (@GTID = 0)
    isn't a check for existance....

    Look at @@ROWCOUNT

    fourth....never mind...fixe the insert first....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •