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
- Once with all variables null,
- once with the values before the trigger execution
- 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
IF((SELECT COUNT(1) INSERTED) = 0)
will never be true. You are missing aFROM
UPDATE dbo.tliefartikel SET fLagerbestand = 0 WHERE @UpdatedComment NOT LIKE '%lager%' AND cSonstiges NOT LIKE '' AND kLiefArtikel = @UpdatedID
is not referencingINSERTED