触发器

最新推荐文章于 2025-06-21 19:32:51 发布

原创最新推荐文章于 2025-06-21 19:32:51 发布 · 567 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#float #insert #up

本文提供了一个MSSQL触发器的实例，包括在新增和更新操作时触发的两种情况，展示了如何通过触发器自动更新库存锁定状态及重量。

无奈，还是第一次写mssql的触发器，企业管理器直接有模板，不错，呵呵

放个demo供以后模仿：

//新增时触发

CREATE TRIGGER [In_EgStock] ON [dbo].[est_outlist]
after insert
AS
declare @New_Weight float
select @New_Weight=weight from inserted

UPDATE P SET
p.isSaleLock = 1 , p.salelockweight = isnull(p.salelockweight,0) +@New_Weight
FROM eg_stock P,est_outlist G where G.stockid=p.stockid

//更新时触发

CREATE TRIGGER [Up_EgStock] ON [dbo].[est_outlist]
after UPDATE
AS
declare @Old_weight float
declare @New_Weight float
declare @isdel int

select @isdel=isdel from inserted
select @Old_weight=weight from deleted

select @New_Weight=weight from inserted

if update(weight)
begin

UPDATE P SET p.isSaleLock = 1 , p.salelockweight = isnull(p.salelockweight,0) + (@New_Weight- @Old_weight) FROM eg_stock P,est_outlist G where G.stockid=p.stockid and G.isdel<>1

UPDATE P SET p.isSaleLock = 0 FROM eg_stock P,est_outlist G where G.stockid=p.stockid and p.salelockweight=0
end

else
if update(isdel)
begin
if @isdel=1
    begin
        UPDATE P SET   p.salelockweight = isnull(p.salelockweight,0) - @New_Weight FROM eg_stock P,est_outlist G where G.stockid=p.stockid
   end
else
if @isdel=0
begin
       UPDATE P SET   p.salelockweight = isnull(p.salelockweight,0) +@New_Weight FROM eg_stock P,est_outlist G where G.stockid=p.stockid
end
end