我希望触发删除触发器 [英] I want delete trigger to be fired
问题描述
创建表instock
（
storeid smallint not null，
wineid smallint not null，
数量smallint not null，
主键（storeid，wineid），
外键（storeid）引用商店，
外键（wineid）引用葡萄酒
）
创建表bigstores
（
storeid smallint not null，
city char（20）not null，
phone char（10）not null，
主键（storeid）
）
如果EXISTS（从sysobjects中选择名称
WHERE Name ='TRI_deleteINSTOCK'AND Type ='P'）
DROP触发器TRI_DELETEINSTOCK
GO
CREATE TRIGGER TRI_deleteINSTOCK
ON instock
FOR delete
AS
BEGIN
DECLARE
@STOREID SMALLINT，
@WINID SMALLINT，
@TOTALQUANTITY INT
SELECT @STOREID = d.STOREID FROM del从
SELECT @wineid = d.wineid来自删除d
SELECT @TOTALQUANTITY = SUM（QUANTITY）
FROM INSTOCK
WHERE STOREID = @ STOREID
从INSTOCK删除
WHERE storeid = @ STOREID AND wineid = @ WINEID
如果不是EXISTS（从店铺中选择店面，店铺地址= @ storeid）
BEGIN
从bigstores删除
其中StoreId = @StoreId AND @totalquantity< = 50000
END
END
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
推荐答案
IF EXISTS（SELECT name from sysobjects
WHERE Name ='TRI_deleteINSTOCK'AND Type ='P'）
DROP触发器TRI_DELETEINSTOCK
GO
CREATE TRIGGER TRI_deleteINSTOCK
ON instock
FOR delete
AS
BEGIN
DECLARE
@STOREID SMALLINT，
@WINID SMALLINT，
@TOTALQUANTITY INT
SELECT @STOREID = d.STOREID FROM deleted d
SELECT @wineid = d.wineid from deleted d
SELECT @TOTALQUANTITY = SUM（QUANTITY）
来自INSTOCK
WHERE STOREID = @ STOREID
从bigstores删除
WHERE storeid = @ STOREID
IF NOT NOT EXISTS（SELECT storeid FROM bigstores WHERE storeid = @ storeid）
BEGIN
从bigstores删除
其中StoreId = @StoreId AND @totalquantity< = 50000
END
END
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 bigstores
WHERE storeid =@STOREID
IF NOT EXISTS(SELECT storeid FROM bigstores WHERE storeid =@storeid )
BEGIN
DELETE FROM bigstores
where StoreId = @StoreId AND @totalquantity <= 50000
END
END
这篇关于我希望触发删除触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
