CHECK for existence of record Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CHECK for existence of record Trigger

Author

Topic

Jmekubo
Starting Member

2 Posts

Posted - 2012-05-06 : 09:07:17

Hi Guys,

I am new to Triggers. I am trying to check if a record exists in my Inventory table.If yes, i need to update the qty column with the inserted qty Else, i need to insert the whole inserted record as a new record in the inventory table.

i have used the following code in my trigger but it keeps inserting new record even after the qty is updated.Please help. The code is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateInventory]
ON [dbo].[GRNS]
AFTER INSERT
AS
BEGIN

DECLARE @Quantity INT
DECLARE @ItemID INT
DECLARE @SupplierID INT
DECLARE @GRNID INT
DECLARE @GRNDetailsID INT
DECLARE @Date SmallDateTime
DECLARE @ItemName VARCHAR(50)
DECLARE @Category VARCHAR(80)
DECLARE @Units NVARCHAR(20)

SELECT @Quantity = (SELECT Quantity FROM Inserted)
SELECT @ItemID = (SELECT ItemID FROM Inserted)
SELECT @SupplierID = (SELECT SupplierID FROM Inserted)
SELECT @GRNID = (SELECT GRNID FROM Inserted)
SELECT @Date = (SELECT Date FROM Inserted)
SELECT @ItemName = (SELECT [Item Name] FROM Inserted)
SELECT @Category = (SELECT Category FROM Inserted)
SELECT @Units = (SELECT Units FROM Inserted)

--Update Inventory table here
IF EXISTS (SELECT Quantity FROM Inserted)
BEGIN
UPDATE dbo.Inventory
SET Quantity = Quantity + @Quantity
WHERE ItemID = @ItemID
END
ELSE

IF @@ROWCOUNT = 0 (SELECT Date,ItemID,[Item Name],Category,Units,Quantity FROM Inserted)
BEGIN

INSERT INTO dbo.Inventory(Date,ItemID,[Item Name],Category,Units,Quantity)
VALUES (@Date,@ItemID,@ItemName,@Category,@Units, @Quantity)

END
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-06 : 10:41:09

I think there is a logical error in the else part. The else part applies only to the statement in red below; so the insertion will happen regardless.

ELSE 

IF @@ROWCOUNT = 0 (SELECT Date,ItemID,[Item Name],Category,Units,Quantity FROM Inserted) 
BEGIN

INSERT INTO dbo.Inventory(Date,ItemID,[Item Name],Category,Units,Quantity)
VALUES (@Date,@ItemID,@ItemName,@Category,@Units, @Quantity)

END
END

The insert trigger is called only once even if the insert statement results in multiple rows being inserted. So the way you have written it - using variables to store the intermediate quantities would not work correctly if more than one row is inserted in a single insert statement. So I would recommend changing the whole code to something like this.

-- update any rows that need to be updated
;WITH cte AS
(
	SELECT
		a.ItemId,
		SUM(a.Quantity) AS NewQuantity
	FROM
		INSERTED a
	GROUP BY
		a.ItemId
)
UPDATE b SET
	Quantity = b.Quantity + a.NewQuantity
FROM
	cte a
	INNER JOIN dbo.Inventory b ON a.ItemId = b.ItemId;

-- insert any new itemids
INSERT INTO dbo.Inventory
([Date],ItemID,[Item Name],Category,Units,Quantity)
SELECT
	MAX([Date]),ItemId,MAX([Item NAME]),MAX(Category),MAX(Units),SUM(Quantity)
FROM
	INSERTED
WHERE ItemId NOT IN (SELECT ItemId FROM dbo.Inventory)
GROUP BY
	ItemId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources