Question

Hello gentlemen,Ladies

I have two tables Quantity and Purchases. When I purchase a product, first I have to check either it's old product or new. If it's Old means I have purchased before and its details already saved in the Database in Quantity Table so it will update the AvailableQuantity field only in the Quantity Table. Else if it's a new Product it must add new row in the Quantity Table with all details of the product.

I hope it's clear for all

I need your comment. How to do it with Trigger ( IF Exist Update else Insert ) or give me another solution.

Answer 1

I think I would wrap the access in a stored procedure. It would then be quite easy to set the logic out so that it is nice and clear.

Answer 2

If you go with Melvyn's solution and you want access to the INSERTED and DELETED tables, you will soon find that these are not available to any stored procedure that you write.

To get around this problem, you can populate a #table with the results of INSERTED (or DELETED). Your stored procedure can be designed to execute with the prerequisite that this #table exists.

Alternatively, I'd keep it simple. Write an UPDATE followed by an INSERT based on JOINs to the magic "INSERTED" and "DELETED" tables. This model works well even when multi-row INSERT/DELETES are issued. E.g...

CREATE TRIGGER [Purchases_Insert]
   ON [Purchases]
   AFTER INSERT, DELETE
    AS 
BEGIN

SET NOCOUNT ON;

--Summarise changes
SELECT ProductID,
	ProductCount
	INTO #ChangeSummary
	FROM ( --Modify to suit your business logic..!
		SELECT ProductID,
			ProductCount = 1
			FROM inserted
		UNION ALL
		SELECT ProductID,
			ProductCount = 0
			FROM deleted
	) AS AffectedProducts

--Update existing Quantity record(s)...
UPDATE Quantity
    SET AvailableQuantity = #ChangeSummary.ProductCount
    FROM Quantity
    	INNER JOIN #ChangeSummary
    		ON Quantity.ProductID = #ChangeSummary.ProductID

--Create new Quantity record(s)...
INSERT Quantity
    (
    ProductID,
    AvailableQuantity
    )
    SELECT #ChangeSummary.ProductID,
    	#ChangeSummary.ProductCount
    	FROM #ChangeSummary
    		LEFT OUTER JOIN Quantity
    			ON #ChangeSummary.ProductID = Quantity.ProductID
    	WHERE Quantity.ProductID IS NULL --I.e. new product

DROP TABLE #ChangeSummary

END

Answer 3

If you're using SQL Server 2008 then I would recommend using MERGE in a trigger, otherwise your If Exists Update Else Insert would be the way to go.

This is assuming, of course, that you can't take Melvyn's suggestion for some reason, which would be the ideal scenario.