x

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 '09 at 10:53 AM in Default

Mohamed gravatar image

Mohamed
34 2 3 3

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

3 answers: sort oldest

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

answered Oct 23 '09 at 08:32 AM

Nick Allan gravatar image

Nick Allan
26 1

(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 '09 at 10:59 AM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(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 '09 at 11:55 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x114
x47

asked: Oct 21 '09 at 10:53 AM

Seen: 2883 times

Last Updated: Oct 22 '09 at 06:50 AM