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 
  • 4
    Easy answer, inside the trigger, you're updating the table that the trigger is on. – Philᵀᴹ Mar 25 '17 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. – Sean Gallardy Mar 26 '17 at 0:06
  • 1
    fixed it with IF TRIGGER_NESTLEVEL() <=1 thx – veritaS Mar 26 '17 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 – Martin Smith Mar 26 '17 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 – Martin Smith Mar 26 '17 at 14:47
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, privacy policy and cookie policy

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