问题是,以下这段代码里面。会获取Insert的CLLLDXH1这个字段。如果获取INSERT代码中没有这个字段,是不是就会造成这个触发器出现错误。
如果会造成错误,这段代码能不能改造成为,既能支持有CLLLDXH1这个字段的INsert,又能支持没有这个字段的。
谢谢!
DECLARE C_TBL_CLCKD1_I CURSOR FOR
SELECT HPID,HPLB,SZCK,PH,SL,JE,CLLLDXH1 FROM INSERTED;
OPEN C_TBL_CLCKD1_I
FETCH NEXT FROM C_TBL_CLCKD1_I INTO @HPID_I,@HPLB_I,@SZCK_I,@PH_I,@SL_I,@JE_I,@CLLLDXH1_I
WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS(SELECT 1 FROM XTHPZL WHERE HPID=@HPID_I AND HPLB=@HPLB_I AND SZCK=@SZCK_I AND PH=@PH_I )
BEGIN
UPDATE XTHPZL SET
SL=ISNULL(SL,0)-ISNULL(@SL_I,0),
JE=ISNULL(JE,0)-ISNULL(@JE_I,0)
WHERE HPID=@HPID_I AND SZCK=@SZCK_I AND PH=@PH_I AND HPLB=@HPLB_I
END
ELSE
BEGIN
INSERT INTO XTHPZL(HPID,HP1,HP2,HP3,HP4,HP5,HP6,HP7,PH,HPLB,SZCK,SL,JE)
SELECT HPID,HP1,HP2,HP3,HP4,HP5,HP6,HP7,@PH_I,HPLB,@SZCK_I,ISNULL(-@SL_I,0),ISNULL(-@JE_I,0) FROM TBL_HPZL WHERE HPID=@HPID_I AND HPLB=@HPLB_I
END
IF EXISTS(SELECT 1 FROM TBL_CLLLD1 WHERE XH1=@CLLLDXH1_I )
UPDATE TBL_CLLLD1 SET
FLG=1
WHERE XH1=@CLLLDXH1_I
FETCH NEXT FROM C_TBL_CLCKD1_I INTO @HPID_I,@HPLB_I,@SZCK_I,@PH_I,@SL_I,@JE_I,@CLLLDXH1_I
END
CLOSE C_TBL_CLCKD1_I
DEALLOCATE C_TBL_CLCKD1_I