Click here to Skip to main content
14,882,311 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In this following query i want to delete particular wineid from instock and it should affect bigstores table but the entered query fails to do
SQL
CREATE TRIGGER TRI_bulkDELETEINSTOCK
ON instock
FOR DELETE
AS
BEGIN
	DECLARE 
		@storeid  smallint, 
		@totalquantity int
		,@wineid smallint
	SELECT @storeid= d.storeid  FROM deleted d
	select @wineid = wineid from instock
	                where wineid =@wineid
	select @totalquantity =  SUM(QUANTITY)
					         FROM instock
					         WHERE storeid = @storeid

	IF  EXISTS(SELECT storeid FROM bigstores WHERE storeid =@storeid  )
	BEGIN
	
		
  delete from bigstores
		where StoreId in (select storeid from instock
		                   where wineid =@wineid
						   group by storeid
						   having @totalquantity <50000
						   )
             
 END
END
GO
Posted
Updated 28-Jan-15 1:32am
v2
Comments
jaket-cp 28-Jan-15 7:31am
   
It could be this @wineid, no value yet and used in where clause:
select @wineid = wineid from instock
where wineid =@wineid
Umer Akram 28-Jan-15 7:32am
   
its a repost of the following question .
Update trigger not firing properly

question is related with that. better ask question on the same thread instead of creating a new one.
ZurdoDev 28-Jan-15 7:40am
   
You can't use variables like that in a trigger. Triggers need to be built to support multiple rows.
TechJosh 28-Jan-15 12:57pm
   
I just want to reiterate what everyone else here has said, you shouldn't be using per-row variables in a table trigger as you don't know that only one row will ever be deleted...

That said, the sql code itself is faulty as well. For instance, this is like a tiger chasing its own tail:
select @wineid = wineid
from instock
where wineid = @wineid

Since @wineid starts off null the where clause specifies that the instock.wineid must be null, which if the database is properly designed, is probably not possible.

It might be possible to write just a DELETE statement as your trigger that uses the deleted table in a subquery to find all StoreIds/wineIds that should be deleted. Perhaps something like this:
DELETE FROM bigstores bs
INNER JOIN deleted d
ON bs.StoreId = d.StoreId
CROSS APPLY (
SELECT SUM (Quantity) as Quantity
FROM instock (NOLOCK)
WHERE instock.StoreId = d.StoreId
) AS Calc
WHERE
Calc.Quantity < 50000

However, bear in mind that this query only handles DELETE statements. If someone were to UPDATE the instock table your trigger wouldn't fire as it is written. You would need to add UPDATE to the list of operations the trigger fires on. You would probably also want a similar trigger on INSERT.

I don't have a clear enough view of your database and use cases. But in this case it seems that you may not actually need the BigStores table anyways and perhaps it could be replaced with a view that only returned StoreIds which met the "bigStores" criteria. Then you wouldn't be caching data which could go stale on you, and you wouldn't need triggers to maintain the table.

CREATE VIEW bigstores
AS
SELECT
StoreId,
SUM(QUANTITY) AS TotalQuantity
FROM
InStock WITH (NOLOCK)
GROUP BY
StoreId

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900