July 28, 2005 at 11:09 am
Im trying to run a procedure that updates multiple rows when multiple rows are deleted. I have already used a delete trigger to achieve this but i only wanted it to be fired when a stored procedure was called. My new code in the stored procedure wont allow me to reurn more than 1 value as its a subquery.
create procedure DeleteOrder
@OrderID int
AS
if not exists(select*from Orders where 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 rderID=@OrderID">OrderID=@OrderID)
from Products p inner join OrderDetails od
on p.ProductID=od.ProductID
delete Orders where rderID=@OrderID">OrderID=@OrderID
how can i update the products table
July 28, 2005 at 11:26 am
I'm not sure about your logic or business rules, but you cannot selectively fire a trigger based off a sproc.
You will prbably have to remove the trigger, and put the logic exclusively in your procedure.
July 28, 2005 at 7:27 pm
Hi,
If I understand ur proc correctly, i believe this will run in a recursive fashion that means, whenever a delete statement is issued on orders table it will again invoked the stored proc and in the stored procedure, it again finds the delete statement and will again call the proc..hope im right in assuming that ur delete trigger calls the deleteorder proc and one more comment here, instead of checking with exists, is it not possible to concatenate the condition in update statement where orderid=@orderid and use orders table in the join..hope i understand ur problem correctly..
Regards,
Dilip
July 29, 2005 at 3:53 am
I dropped the delete trigger which is below as i only needed it to update my products table. But because i tried to move the code into a stored procedure i no longer needed the trigger. What im trying to do is to recreate this trigger in a stored procedure
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
go
thanks
July 31, 2005 at 7:07 pm
Christopher - here's another workaround if you want to consider this....
if you want the trigger to be fired only when the stored procedure deletes from the table, then you can always keep your trigger asis and do a :
ALTER TABLE trigTable DISABLE TRIGGER myTrigger...
before you delete via the application (or wherever else you're deleting from) and then enable it again with....
ALTER TABLE trigTable ENABLE TRIGGER myTrigger
this way the only time it'll be fired is when the table is deleted via the proc.
**ASCII stupid question, get a stupid ANSI !!!**
July 31, 2005 at 8:04 pm
afterthought addendum...
but then again not knowing what permissions issues are involved for disabling/enabling the trigger...maybe you are better off putting everything inside a stored procedure...!!!!!
**ASCII stupid question, get a stupid ANSI !!!**
August 1, 2005 at 10:01 am
I've done something similar by using a second table to determine whether the trigger should be active.. Your procedure would then update the table.
The trigger would only fire after checking the second table whether the trigger should be fired. This would circumvent some of the permissions issues that Sushila mentions...
Hope that helps...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
