create table instock
(
storeid smallint not null,
wineid smallint not null,
quantity smallint not null,
primary key (storeid, wineid),
foreign key (storeid) references stores,
foreign key (wineid) references wines
)
create table bigstores
(
storeid smallint not null,
city char(20) not null,
phone char(10) not null,
primary key (storeid)
)
IF EXISTS ( SELECT Name FROM sysobjects
WHERE Name = 'TRI_deleteINSTOCK' AND Type = 'P' )
DROP trigger TRI_DELETEINSTOCK
GO
CREATE TRIGGER TRI_deleteINSTOCK
ON instock
FOR delete
AS
BEGIN
DECLARE
@STOREID SMALLINT,
@WINEID SMALLINT,
@TOTALQUANTITY INT
SELECT @STOREID = d.STOREID FROM deleted d
SELECT @wineid = d.wineid from deleted d
SELECT @TOTALQUANTITY = SUM(QUANTITY)
FROM INSTOCK
WHERE STOREID =@STOREID
DELETE FROM INSTOCK
WHERE storeid =@STOREID AND wineid =@WINEID
IF NOT EXISTS(SELECT storeid FROM bigstores WHERE storeid =@storeid )
BEGIN
DELETE FROM bigstores
where StoreId = @StoreId AND @totalquantity <= 50000
END
END