Trigger fires twice

Question

Post reply

Trigger fires twice

Tomas Haglund

Mr or Mrs. 500

Points: 551
More actions
December 19, 2011 at 12:41 am

#99566

Have vb routine that run proc with ExecuteNonQuery. The proc inserts row in table A. Table A has a trigger that inserts row in table B. The problem is that the trigger fires twice for one inserted row. My VB-code is:
For iIndex = 0 To iArtikelRows - 1
cmd01.Parameters.AddWithValue("@idFakt", insertedId)
cmd01.Parameters.AddWithValue("@p5", "INTERNT")
sName = "tbBenamning-" + Trim(Str(iIndex))
sAntal = "tbAntal-" + Trim(Str(iIndex))
sApris = "tbApris-" + Trim(Str(iIndex))
cmd01.Parameters.AddWithValue("@p10", Request.Form(sName))
If Len(Request.Form(sAntal)) > 0 Then
cmd01.Parameters.AddWithValue("@p15", Replace(Request.Form(sAntal), ",", "."))
Else
cmd01.Parameters.AddWithValue("@p15", Request.Form(sAntal))
End If
If Len(Request.Form(sApris)) > 0 Then
cmd01.Parameters.AddWithValue("@p20", Replace(Request.Form(sApris), ",", "."))
Else
cmd01.Parameters.AddWithValue("@p20", Request.Form(sApris))
End If
cmd01.Parameters.AddWithValue("@p72", sRegistrator)
cmd01.ExecuteNonQuery()
cmd01.Parameters.Clear()
Next
I have tried to debug and I can't see anything wrong. Perhapes data cache ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

How are you tracing that? (that the trigger executed twice)

Is the insert happening twice?

Can you post the trigger code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tomas Haglund Mr or Mrs. 500 Points: 551 More actions · Answer 2

I can see it in the table B.

Here is the trigger:

ALTER TRIGGER [FidInsertRecordsToArtikles]

ON [dbo].[TblWebArtikles]

AFTER INSERT

AS

DECLARE @err int

BEGIN

DECLARE @p1 varchar(50)

DECLARE @idFakt int

DECLARE @artikelsumma numeric(11,2)

DECLARE @TOTAL numeric(11,2)

SET @TOTAL = '0.00'

SET @p1 = ''

SET @idFakt = 0

SET NOCOUNT ON;

SELECT @p1=Receiver,@idFakt=idFaktura FROM INSERTED

END

BEGIN TRANSACTION

-- /////////////////////////////////////////////////////////////////////////////////

-- Artikelrad

-- /////////////////////////////////////////////////////////////////////////////////

-- Rensa bort rader som inte har belopp

DELETE FROM tblArtiklar WHERE Receiver = @p1 AND LEN(Pris) = 0

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN END

INSERT INTO tblArtiklar (idFaktura,Receiver,Artikelnummer,Artikeltext,Antal,Pris)

SELECT idFaktura,Receiver,Art,Ben,Antal,Pris

FROM TblWebArtikles where Receiver = @p1 and idFaktura = @idFakt

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN END

-- Cursor för summering av artikelsumma

DECLARE @antal nvarchar(50),@Pris nvarchar(50)

DECLARE summering_cursor CURSOR FOR

SELECT Antal,Pris

FROM TblWebArtikles where Receiver = @p1 and idFaktura = @idFakt

OPEN summering_cursor

FETCH NEXT FROM summering_cursor

INTO @antal,@Pris

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Artikelsumma = 0

SELECT @Artikelsumma = CASE WHEN (ISNUMERIC(@Antal)=1 AND ISNUMERIC(@Pris)=1) THEN CONVERT(numeric(11,2),@Antal) * CONVERT(numeric(11,2),@Pris)

ELSE '0.00'

END

SET @Total = @total + @artikelsumma

FETCH NEXT FROM summering_cursor

INTO @antal,@Pris

END

CLOSE summering_cursor

DEALLOCATE summering_cursor

-- Rensa bort rader som inte har belopp

DELETE FROM tblArtiklar WHERE Receiver = @p1 AND (LEN(Pris) = 0 OR LEN(Antal) = 0)

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN END

-- För in summan av artikelbeloppen i Fakturahuvudet

UPDATE TblFakturahuvud SET Belopp= LTRIM(STR(@TOTAL,11,2)) WHERE Receiver = @p1 AND idFaktura = @idFakt

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN END

COMMIT TRANSACTION

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Run a profile trace to be completely sure whether

1) the insert into TblWebArtikles isn't happening twice

2) the trigger is running twice (which is not possible unless the insert has run twice)

What are you looking at in TableB to tell that the trigger ran twice?

Have you considered adding debugging code to the trigger (like statements that insert into a logging table) so that you can tell exactly what happens?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323482 More actions · Answer 4

also, make sure you don't have two triggers on the same table, both with the same basic code, but two different names.

that might explain two rows inserted when you expect one, but it wouldn't be obvious because you only look at one specific trigger.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tomas Haglund Mr or Mrs. 500 Points: 551 More actions · Answer 5

Used Profiler and did not see anything wrong but I saw that the trigger code generated multiple rows, look here:

INSERT INTO tblArtiklar (idFaktura,Receiver,Artikelnummer,Artikeltext,Antal,Pris)

SELECT idFaktura,Receiver,Art,Ben,Antal,Pris

FROM TblWebArtikles where Receiver = @p1 and idFaktura = @idFakt

I replace TblWebArtikles with INSERTED and that fix the problem.

Logical error :hehe:

Thanks!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Excellent. Glad you figured it out.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass