Sql-server – Why is this trigger getting executed two times

sql serversql-server-2008t-sqltrigger

I have created a trigger to modify some database when a specific value is getting imported/changed in the comment field to set the quantity of this product to 0.

So for the last delete/insert (– insert info for updated) part of the trigger I always get errors "cannot insert null".

So I created a test with test table(testingtable) to insert the actual variables.
For some reason the trigger is always getting executed 3 times or runs a loop, so it imports 3 rows in the test table

  1. Once with all variables null,
  2. once with the values before the trigger execution
  3. once with the new variables

Does someone can help me understand why the insert statement is execute 3 times?
or how I can test for this?

ALTER TRIGGER [dbo].[tf_Tlieferartikel]
ON [dbo].[tliefartikel]
AFTER UPDATE
AS
    SET NOCOUNT ON;
    SET ANSI_NULLS ON;
    SET ANSI_NULL_DFLT_ON ON;
    SET ANSI_PADDING ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET XACT_ABORT OFF;

  BEGIN
      --Überprüfen ob Trigger gefüllt aufgerufen wird
      IF( (SELECT COUNT(1) INSERTED) = 0 )
        BEGIN
            RETURN;
        END

      DECLARE @UpdatedComment  VARCHAR (255),
              @UpdatedID       INT,
              @UpdatedKartikel INT,
              @UpdatedQuantity INT,
              @UpdatedkItem    INT

      SELECT @UpdatedComment = INSERTED.cSonstiges,
             @UpdatedID = INSERTED.kLiefArtikel,
             @UpdatedKartikel = INSERTED.tArtikel_kArtikel,
             @UpdatedQuantity = (SELECT TOP 1 quantity
                                 FROM   [database].[dbo].[ebay_item]
                                 WHERE  kArtikel = INSERTED.tArtikel_kArtikel
                                        AND type = 'L'),
             @UpdatedkItem = (SELECT TOP 1 [kItem]
                              FROM   [database].[dbo].[ebay_item]
                              WHERE  kArtikel = INSERTED.tArtikel_kArtikel
                                     AND type = 'L')
      FROM   INSERTED

      -- Update des Lieferartikel
      UPDATE dbo.tliefartikel
      SET    fLagerbestand = 0
      WHERE  @UpdatedComment NOT LIKE '%lager%'
             AND cSonstiges NOT LIKE ''
             AND kLiefArtikel = @UpdatedID

      --or @NewComment NOT LIKE '%lager%'
      -- Update des Artikels aus der Summer des Lieferartikels lagerbestand
      UPDATE [database].[dbo].[tArtikel]
      SET    nLagerbestand = (SELECT SUM(fLagerbestand)
                              FROM   [database].[dbo].[tliefartikel]
                              WHERE  tArtikel_kArtikel = @UpdatedKartikel)
      WHERE  [kArtikel] = @UpdatedKartikel

      -- Update der aktiven ebay angebotsvorlagen aus der Summer des  Lieferanartikel lagerbestandes, wenn größer 5 dann 5 weil derzeit alle vorlagen max 5 quantity haben
      UPDATE [database].[dbo].[ebay_item]
      SET    [Quantity] = CASE
                            WHEN (SELECT SUM(fLagerbestand)
                                  FROM   [database].[dbo].[tliefartikel]
                                  WHERE  tArtikel_kArtikel = @UpdatedKartikel) <= 5
                              THEN (SELECT SUM(fLagerbestand)
                                    FROM   [database].[dbo].[tliefartikel]
                                    WHERE  tArtikel_kArtikel = @UpdatedKartikel)
                            ELSE 5
                          END
      WHERE  SKU = (SELECT cArtNr
                    FROM   [database].[dbo].[tArtikel]
                    WHERE  [kArtikel] = @UpdatedKartikel)
             AND Type = 'L';

      -- Insert test    
      IF @UpdatedkItem IS NOT NULL
        INSERT INTO TF.testingtable
                    (UpdatedComment,
                     UpdatedID,
                     UpdatedKartikel,
                     UpdatedQuantity,
                     UpdatedkItem)
        VALUES      (@UpdatedComment,
                     @UpdatedID,
                     @UpdatedKartikel,
                     @UpdatedQuantity,
                     @UpdatedkItem )
  -- insert info for updated
  /*DELETE            [database].[dbo].[ebay_geaenderte_laufende_angebote] where kitem = @UpdatedkItem
  insert into [database].[dbo].[ebay_geaenderte_laufende_angebote] (kItem, nchanges, nTryUpload)
  values (@UpdatedkItem,
  CASE
      WHEN @UpdatedQuantity = 0
      THEN 4
      ELSE 2
  END 
  ,1);*/
  END 

Best Answer

Simply : It triggers on [dbo].[tliefartikel] UPDATES and does an UPDATE to this table itself.

This is recursivity.