tab_product
pid pname stocks
p1001 足球 100
tab_ingoods
iid pid stocks
1001 p1001 50
编写一个触发器,当成功更新一条进货记录的进货量时,及时更新库存量. 问题:
(1) 如果员工对进货表的stocks +50,那么写的触发器,就需在inserted表当中该值相加.但如果是-50呢?我应该如何让触发器失败对表是进行相加还是相减??
(2) 进货表的stocks 是不能少于0的,那么应该如何识别如果员工对进货表的stocks进行相减时少于当前值呢??
我写的触发器是:IF OBJECT_ID('tg_update_ingoods_product','TR')IS NOT NULL
DROP TRIGGER tg_update_ingoods_product
GO
--创建触发器并加密
CREATE TRIGGER tg_update_ingoods_product ON tab_ingoods WITH ENCRYPTION FOR UPDATE
AS
DECLARE @pname VARCHAR(20)
UPDATE tab_product
SET tab_product.stocks = tab_product.stocks+inserted.stocks
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid
SELECT @pname = tab_product.pname
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid
PRINT '你此次操作成功,并且成功的更新了'+@pname+'的库存量!'
如果我进行相加操作:UPDATE tab_ingoods
SET stocks = stocks+500
WHERE pid = 'p1001'原表会变成:
tab_product
pid pname stocks
p1001 足球 600
tab_ingoods
iid pid stocks
1001 p1001 550如果在上面的基础进行相减操作:
UPDATE tab_ingoods
SET stocks = stocks-500
WHERE pid = 'p1001'效果就却变成
tab_product
pid pname stocks
p1001 足球 600
tab_ingoods
iid pid stocks
1001 p1001 50
进行相减的时候,产品表是无法减少的.请问如果解决这个问题啊???
pid pname stocks
p1001 足球 100
tab_ingoods
iid pid stocks
1001 p1001 50
编写一个触发器,当成功更新一条进货记录的进货量时,及时更新库存量. 问题:
(1) 如果员工对进货表的stocks +50,那么写的触发器,就需在inserted表当中该值相加.但如果是-50呢?我应该如何让触发器失败对表是进行相加还是相减??
(2) 进货表的stocks 是不能少于0的,那么应该如何识别如果员工对进货表的stocks进行相减时少于当前值呢??
我写的触发器是:IF OBJECT_ID('tg_update_ingoods_product','TR')IS NOT NULL
DROP TRIGGER tg_update_ingoods_product
GO
--创建触发器并加密
CREATE TRIGGER tg_update_ingoods_product ON tab_ingoods WITH ENCRYPTION FOR UPDATE
AS
DECLARE @pname VARCHAR(20)
UPDATE tab_product
SET tab_product.stocks = tab_product.stocks+inserted.stocks
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid
SELECT @pname = tab_product.pname
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid
PRINT '你此次操作成功,并且成功的更新了'+@pname+'的库存量!'
如果我进行相加操作:UPDATE tab_ingoods
SET stocks = stocks+500
WHERE pid = 'p1001'原表会变成:
tab_product
pid pname stocks
p1001 足球 600
tab_ingoods
iid pid stocks
1001 p1001 550如果在上面的基础进行相减操作:
UPDATE tab_ingoods
SET stocks = stocks-500
WHERE pid = 'p1001'效果就却变成
tab_product
pid pname stocks
p1001 足球 600
tab_ingoods
iid pid stocks
1001 p1001 50
进行相减的时候,产品表是无法减少的.请问如果解决这个问题啊???
我不知道怎么再说清楚了.你根据我说的建一个表就知道了.我写的那个存储过程只能实现这个句子:UPDATE tab_ingoods
SET stocks = stocks+500
WHERE pid = 'p1001' 但不能实现:
UPDATE tab_ingoods
SET stocks = stocks-500
WHERE pid = 'p1001'
我不知道怎么再说清楚了.你根据我写的那个存储过程试试就知道了.我写的那个UPDATE存储过程只能实现这个语句:
UPDATE tab_ingoods
SET stocks = stocks+500
WHERE pid = 'p1001' 但不能实现这个:
UPDATE tab_ingoods
SET stocks = stocks-500
WHERE pid = 'p1001' 相加的话,货存能正常处理.但如果相减的话,货存不会变...
DROP TRIGGER tg_update_ingoods_product
GO
--创建触发器并加密
CREATE TRIGGER tg_update_ingoods_product ON tab_ingoods WITH ENCRYPTION FOR UPDATE
AS
DECLARE @pname VARCHAR(20)
DECLARE @QTY int
select @QTY=a.stocks-b.stocks from inserted a,deleted b
where a.pid=b.pid
UPDATE tab_product
SET tab_product.stocks = tab_product.stocks+@QTY
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid
SELECT @pname = tab_product.pname
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid PRINT '你此次操作成功,并且成功的更新了'+@pname+'的库存量!'
SET stocks = stocks+50
WHERE pid = 'p1001'
执行两遍,你看看结果!!!
AS
DECLARE @pname VARCHAR(20)
UPDATE tab_product
SET tab_product.stocks = tab_product.stocks+inserted.stocks-DELETED.stocks
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid JOIN DELETED ON DELETED.pid = tab_product.pid
SELECT @pname = tab_product.pname
FROM tab_product JOIN inserted ON inserted.pid = tab_product.pid
PRINT '你此次操作成功,并且成功的更新了'+@pname+'的库存量!'
pid pname stocks
-------------------- -------------------- -----------
p1001 足球 450
SELECT * FROM tab_ingoods
iid pid stocks
-------------------- -------------------- -----------
1001 p1001 450DECLARE @MyTableVar table(iid nvarchar(20), pid nvarchar(20), [INSERTED.stocks] int,[原值] int);
UPDATE tab_ingoods
SET stocks = stocks+20
OUTPUT INSERTED.iid,
INSERTED.pid,
INSERTED.stocks,
DELETED.stocks
INTO @MyTableVar
WHERE pid = 'p1001' select * from @MyTableVariid pid INSERTED.stocks 原值
-------------------- -------------------- --------------- -----------
1001 p1001 470 450lz看出问题了吧