How to Call Procedure from Trigger ?

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.

more ▼

asked Oct 21, 2009 at 10:53 AM in Default

avatar image

34 3 3 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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...



--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


more ▼

answered Oct 23, 2009 at 08:32 AM

avatar image

Nick Allan
26 1 2 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 21, 2009 at 10:59 AM

avatar image

Melvyn Harbour 1 ♦♦
1.4k 19 41 26

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 21, 2009 at 11:55 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 21, 2009 at 10:53 AM

Seen: 3565 times

Last Updated: Oct 22, 2009 at 06:50 AM

Copyright 2018 Redgate Software. Privacy Policy