return multiple rows in subquery
-
i have created a trigger that updates multiple row when a delete occurs but the problem is that i only want it to fire when the delete is from a stored procedure. Because this cant be done i moved the code to the stored procedure but it can only update 1 row at a time. Any solutions please
-
why can it update only one row ?! what is your update based on ?! could you pl. provide some more details ?!
**ASCII stupid question, get a stupid ANSI !!!** -
I just saw your other post....there was someone yesterday who seemed to have a novel approach to storing all the db object create scripts in a stored procedure and then executing the procedure....based on that, here's something that you could play around with....the problem is you'll have to keep creating and dropping the trigger...but it's one way that I could think of...sure someone else will have a better idea you could work with...until then this could be a workaround....
Create Procedure upFireTrigger
AS
DECLARE @Sql VarChar(2000)
SET @Sql = 'create trigger DeletedOrders on OrderDetails for Delete
AS
if exists(select*from Deleted where DispatchedDate < getdate())
BEGIN
RAISERROR(' + "This product has already been dispatched" + ',16,1)
ROLLBACK
RETURN
END
Update Products set UnitsInStock=UnitsInStock+d.Quantity
from Products p inner join Deleted d on p.ProductID=d.ProductID'
EXEC @Sql
go
**ASCII stupid question, get a stupid ANSI !!!** -
create procedure DeleteOrder
@OrderID int
AS
if not exists(select*from Orders where <A href="mailto
rderID=@OrderID">OrderID=@OrderID)BEGIN
RAISERROR('The selected order was not made',16,1)
RETURN
END
update Products set UnitsInStock=UnitsInStock+
(select Quantity from OrderDetails where <A href="mailto
rderID=@OrderID">OrderID=@OrderID)from Products p inner join OrderDetails od
on p.ProductID=od.ProductID
delete Orders where <A href="mailto
rderID=@OrderID">OrderID=@OrderIDthis is the stored proc i tried to use. I though the create trigger statement had to be the first in the batch but ill try it out
thanks
-
Christopher - another workaround that I can think of is to use a flag in your OrderDetails table - something that will indicate whether the rows have been deleted via the procedure or application (??)
You could then check this flag in your "deleted" table and then proceed accordingly - ie. if deleted from proc - then do this - else do nothing!
meanwhile - could you please post the ddls of your OrderDetails, Order & Products table and what you want to accomplish - is it just that if the products have not been dispatched by current date they get added to the unitsinstock in the products table ?!?! or is there more ?
**ASCII stupid question, get a stupid ANSI !!!** -
forget what I said about the flag..don't know what I was talking about...
just post the ddls & what you want to do!
**ASCII stupid question, get a stupid ANSI !!!** -
You can use a cursor to load up a set of rows and then perform an update on each one.
Example code for end of sproc:
-- Holder for row data
DECLARE @Holder nvarchar(255)
-- This is a select statement to get all the rows you want to modify
DECLARE cur_Update CURSOR FOR
SELECT something FROM table WHERE something
-- Open cursor and read in first record
OPEN cur_Update
FETCH NEXT FROM cur_Update INTO @Holder
WHILE @@FETCH_STATUS = 0
BEGIN
-- Perform action on row
UPDATE table
SET something
WHERE something = @Holder
FETCH NEXT FROM cur_Update INTO @Holder
END
CLOSE cur_Update
DEALLOCATE cur_Update
-
Bo - Cursors are a big "NO NO" on this site (& should be everywhere else) - the idea is to be efficient and get all the rows in one fell swoop...
You will have all the fanatical "CursorsAreEvil" people descending upon you for your post...so be warned!
**ASCII stupid question, get a stupid ANSI !!!** -
create table OrderDetails(
OrderID int not null REFERENCES Orders(OrderID)on delete cascade,
ProductID int not null REFERENCES Products(ProductID),
OrderedDate smalldatetime not null CHECK (OrderedDate>=datepart(day,getdate())),
Quantity int not null DEFAULT 1,
UnitPrice money not null,
RequiredDate smalldatetime null CHECK
(datepart(day,RequiredDate)>=datepart(day,getdate())),
DispatchedDate smalldatetime null CHECK
(datepart(day,DispatchedDate)>=datepart(day,getdate())),
PRIMARY KEY (OrderID, ProductID)
 
when an order is made the units in stock in my products table is deducted by the order quantity. When an order that has not yet been dispatched is deleted i am trying to update the products table so that the units in stock is what it was before the order was made.
-
Christopher - I don't have time to test this but see if this is what you want...
CREATE PROCEDURE DeleteOrder
@OrderID int
AS
IF EXISTS(SELECT * FROM OrderDetails WHERE OrderID = @OrderID AND DispatchedDate < getdate())
BEGIN
RAISERROR('This product has already been dispatched',16,1)
RETURN
END
ELSE
BEGIN
UPDATE Products SET UnitsInStock = (UnitsInStock + d.Quantity)
FROM Products P INNER JOIN OrderDetails OD ON P.ProductID = OD.ProductID
WHERE OD.OrderID = @OrderID
DELETE FROM OrderDetails WHERE OrderID = @OrderID
END
GO
Btw - are you not keeping track of your cancelled orders at all ?! In your orders table, do you have a cancelled date ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Given the scenario a set based update will work as well and would be more efficient as the gentleman posted above. Would put something like this at the beginning of the sproc before you delete the record(s).
This is assuming QOH in your products table is the inventory quantity and your deleting based on @OrderID
UPDATE Products
SET QOH = QOH + od.Quantity
FROM Products p INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
WHERE od.OrderID = @OrderID
That said I do think cursors serve a purpose when a set based method simply will not work or a more procedural approach is desired. To me they are similar to arrays and loops when programming using c# or java or whatever. I agree with the fellows that say a set method is most often more efficient; but cursors are there and available to be used so I wouldn't throw them out. Just my 2 cents and everyone has their own opinion.
-
...but cursors are there and available to be used so I wouldn't throw them out. Just my 2 cents and everyone has their own opinion......
yes indeed Bo...they're not being thrown out...just reserved for a "when all else fails" situation...
ps: If you were referring to Christopher as the gentleman I won't argue with you but if you were referring to me I must confess to being called many things in my life but never that...
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply