store表:
store_id 自动增长ID
sproduct_id 商品ID
spnumber 数量
purchase表:
purchase_id 自动增长ID
pproduct_id 商品ID
punumber 数量我的删除过程描述如下:
我删除purchase表的一条记录,然后获得删除这条记录punumber,然后store表更新把字段
spnumber=store.spnumber-purchase.punumber
单独删除的触发器代码如下:
use jxc
go
create trigger sp_delete
on purchase
after delete
as
begin
declare @number int;
select @number=sum(pc.punumber) from deleted as pc;
if exists(select sproduct_id from store where sproduct_id=(select pproduct_id from deleted))
begin
update store set spnumber=(spnumber-@number)where store.sproduct_id=(select pproduct_id from deleted)
END
end
删除多个的触发器怎么写知道说一下谢谢。有空的可以写一下代码谢谢。SQL触发器
store_id 自动增长ID
sproduct_id 商品ID
spnumber 数量
purchase表:
purchase_id 自动增长ID
pproduct_id 商品ID
punumber 数量我的删除过程描述如下:
我删除purchase表的一条记录,然后获得删除这条记录punumber,然后store表更新把字段
spnumber=store.spnumber-purchase.punumber
单独删除的触发器代码如下:
use jxc
go
create trigger sp_delete
on purchase
after delete
as
begin
declare @number int;
select @number=sum(pc.punumber) from deleted as pc;
if exists(select sproduct_id from store where sproduct_id=(select pproduct_id from deleted))
begin
update store set spnumber=(spnumber-@number)where store.sproduct_id=(select pproduct_id from deleted)
END
end
删除多个的触发器怎么写知道说一下谢谢。有空的可以写一下代码谢谢。SQL触发器
改这样么,也不行。
BEGIN
DROP TABLE dbo.T_CSDN_ONE;
END
GO
CREATE TABLE T_CSDN_ONE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[NAME] VARCHAR(50),
NUMBER INT
)
INSERT INTO dbo.T_CSDN_ONE
SELECT 'A',20
UNION ALL
SELECT 'B',20
UNION ALL
SELECT 'A',10
UNION ALL
SELECT 'A',5
UNION ALL
SELECT 'B',40
UNION ALL
SELECT 'B',10
SELECT * FROM dbo.T_CSDN_ONE
IF OBJECT_ID('T_CSDN_TWO') IS NOT NULL
BEGIN
DROP TABLE dbo.T_CSDN_TWO;
END
GO
CREATE TABLE T_CSDN_TWO
(
[NAME] VARCHAR(50),
[SUMNUMBER] INT
)
INSERT INTO T_CSDN_TWO
SELECT 'A',35
UNION ALL
SELECT 'B',70
GO
CREATE TRIGGER TR_DELETE
ON T_CSDN_ONE
AFTER DELETE
AS
BEGIN
UPDATE T_CSDN_TWO SET SUMNUMBER=SUMNUMBER-(SELECT ISNULL(SUM(NUMBER),0) FROM DELETED WHERE NAME=T_CSDN_TWO.NAME)
END
DELETE T_CSDN_ONE WHERE ID IN (4,3,5)
SELECT * FROM dbo.T_CSDN_ONE
SELECT * FROM dbo.T_CSDN_TWO
gocreate trigger sp_delete
on purchase after delete
as
begin
update a
set a.spnumber=a.spnumber-b.punumber
from store a
inner join
(select pproduct_id,
sum(punumber) 'punumber'
from deleted
group by pproduct_id) b on a.sproduct_id=b.pproduct_id
end