3

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 
9
  • 4
    Easy answer, inside the trigger, you're updating the table that the trigger is on.
    – Philᵀᴹ
    Mar 25, 2017 at 23:00
  • 1
    You'd have to do some testing with the nested trigger setting. The best way would be to figure how to NOT use a trigger, IMNSHO. Mar 26, 2017 at 0:06
  • 1
    fixed it with IF TRIGGER_NESTLEVEL() <=1 thx
    – veritaS
    Mar 26, 2017 at 0:08
  • 2
    That trigger has other problems too. It will not work correctly if an update affects multiple rows and IF((SELECT COUNT(1) INSERTED) = 0) will never be true. You are missing a FROM Mar 26, 2017 at 14:32
  • 2
    Also looks suspect that UPDATE dbo.tliefartikel SET fLagerbestand = 0 WHERE @UpdatedComment NOT LIKE '%lager%' AND cSonstiges NOT LIKE '' AND kLiefArtikel = @UpdatedID is not referencing INSERTED Mar 26, 2017 at 14:47

1 Answer 1

1

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

This is recursivity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.