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

ThanksLooks 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|||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|||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|||No Luck :-(|||Can you post the code for your trigger

Cheers|||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|||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..|||Just got back from lunch mate

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

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