Fetch next

最新推荐文章于 2024-08-17 22:11:24 发布

原创最新推荐文章于 2024-08-17 22:11:24 发布 · 981 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个SQL触发器的实现方式，用于批量更新特定条件下的需求量数据。通过设置存储过程和触发器，针对LWO_REQ_WF表中的插入操作，自动更新需求量字段。特别关注了对06和A06开头的项号进行特殊处理，确保需求量的正确计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [TIX_LWO_REQ_WF_SPS] ON [dbo].[LWO_REQ_WF]
--DROP TRIGGER TIX_LWO_REQ_WF_SPS
--WITH ENCRYPTION
--MDF 06 AND A06 REQ_QT
FOR INSERT AS
BEGIN
DECLARE

@CO_CD NVARCHAR(4),
@DIV_CD NVARCHAR(4),
@WO_CD NVARCHAR(12),
@ITEM_CD NVARCHAR(30),
@REQ_QT NUMERIC(17,6),
@WOBOM_SQ NUMERIC(5)

DECLARE CUR_TIX_LWO_REQ_WF_SPS CURSOR FOR
SELECT CO_CD, WO_CD,WOBOM_SQ
FROM INSERTED

OPEN CUR_TIX_LWO_REQ_WF_SPS
FETCH NEXT FROM CUR_TIX_LWO_REQ_WF_SPS
INTO @CO_CD ,@WO_CD ,@WOBOM_SQ

WHILE(@@FETCH_STATUS=0)
BEGIN
--SELECT * FROM LWO_REQ_WF
UPDATE LWO_REQ_WF
SET REQ_QT = CASE WHEN LEFT(ITEM_CD,2) = '06' OR LEFT(ITEM_CD,3) = 'A06'
                     THEN CASE WHEN REQ_QT < FLOOR(REQ_QT)+0.000001
                          THEN REQ_QT ELSE FLOOR(REQ_QT)+1 END
                     ELSE REQ_QT END,
     JUST_QT = CASE WHEN LEFT(ITEM_CD,2) = '06' OR LEFT(ITEM_CD,3) = 'A06'
                     THEN CASE WHEN REQ_QT < FLOOR(REQ_QT)+0.000001
                          THEN REQ_QT ELSE FLOOR(REQ_QT)+1 END
                     ELSE REQ_QT END,
     isureq_qt = CASE WHEN LEFT(ITEM_CD,2) = '06' OR LEFT(ITEM_CD,3) = 'A06' -- add by lhj 20121127
                     THEN CASE WHEN REQ_QT < FLOOR(REQ_QT)+0.000001
                          THEN REQ_QT ELSE FLOOR(REQ_QT)+1 END
                     ELSE REQ_QT END
WHERE CO_CD = @CO_CD AND WO_CD = @WO_CD AND WOBOM_SQ = @WOBOM_SQ