权当纪念.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'TRIGGER_JYGL_DZCJJS' AND type = 'TR')
DROP TRIGGER TRIGGER_JYGL_DZCJJS
GO
CREATE TRIGGER TRIGGER_JYGL_DZCJJS
ON JYGL_DZCJJS
AFTER INSERT, UPDATE, DELETE
AS
DECLARE
@YEAR INT,
@KTXM VARCHAR(30),
@PXH INT,
@DH VARCHAR(50),
@GQ VARCHAR(30),
@WB VARCHAR(30),
@GZL real,
@PS int,
@QTFY real,
@ZJ real,
@SGF real,
@ZJTZ real
BEGIN
/* 判读是否是有效修改操作 */
IF (SELECT COUNT(*) FROM INSERTED) > 0
SELECT @DH=DH FROM INSERTED
ELSE
SELECT @DH=DH FROM DELETED
IF @DH<>'小计' AND @DH<>'合计'
BEGIN
IF (SELECT COUNT(*) FROM INSERTED) > 0
BEGIN
SELECT @PXH=PXH,@YEAR=ND,@KTXM=KTXM,@DH=DH,@GQ=GQ,@WB=WB FROM INSERTED
/* 判断是update还是insert */
IF (SELECT COUNT(*) FROM DELETED) = 0
/* INSERT 需要计算排序号 */
BEGIN
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计' AND PXH IS NOT NULL) > 0
SELECT TOP 1 @PXH=PXH FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计' AND PXH IS NOT NULL
ELSE
BEGIN
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND PXH IS NOT NULL) > 0
SELECT @PXH=ISNULL(MAX(PXH),0)+1 FROM JYGL_DZCJJS WHERE DH<>'合计'
ELSE
SELECT @PXH=ISNULL(MAX(PXH),0)+1 FROM JYGL_DZCJJS
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH
END
UPDATE JYGL_DZCJJS SET PXH=@PXH WHERE ND=@YEAR AND KTXM=@KTXM AND DH=@DH AND GQ=@GQ AND WB=@WB
END
/* 重新小计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计'
ELSE
BEGIN
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH+1,@YEAR,@KTXM,'小计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 二维合计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='二维'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='二维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='二维'
ELSE
BEGIN
SELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计'
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','二维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 三维合计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='三维'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='三维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='三维'
ELSE
BEGIN
SELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND (DH<>'合计' OR WB='二维')
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','三维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 总计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维'
ELSE
BEGIN
SELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
END
ELSE
BEGIN
SELECT @PXH=PXH,@YEAR=ND,@KTXM=KTXM,@DH=DH,@GQ=GQ,@WB=WB FROM DELETED
/* 判断该勘探项目是否还存在数据记录 */
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM and DH<>'小计') > 0
/*该勘探项目还存在记录修改小计和合计即可*/
BEGIN
/* 重新小计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计'
ELSE
BEGIN
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH+1,@YEAR,@KTXM,'小计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 二维合计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='二维'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='二维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='二维'
ELSE
BEGIN
SELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计'
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','二维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 三维合计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='三维'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='三维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='三维'
ELSE
BEGIN
SELECT @PXH=PXH+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计' AND WB='二维'
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','三维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 总计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维'
ELSE
BEGIN
SELECT @PXH=PXH+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND WB='三维'
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
END
ELSE
/* 该勘探项目已经没有记录 删除小计 重新合计 */
BEGIN
DELETE FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计'
UPDATE JYGL_DZCJJS SET PXH=PXH-2 WHERE ND=@YEAR AND PXH>@PXH
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计') > 0
/* 重新合计 */
BEGIN
/* 二维合计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='二维'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='二维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='二维'
ELSE
BEGIN
SELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计'
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','二维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 三维合计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='三维'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='三维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='三维'
ELSE
BEGIN
SELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND (DH<>'合计' OR WB='二维')
UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','三维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
/* 总计 */
SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计'
IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维') > 0
UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维'
ELSE
BEGIN
SELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR
INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)
END
END
ELSE
DELETE FROM JYGL_DZCJJS WHERE ND=@YEAR AND (DH='小计' OR DH='合计')
END
END
END
END
GO
是值得庆幸还是一种悲哀?
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TRIGGER_JYGL_DZCJJS' AND type = 'TR') DROP TRIGGER TRIGGER_JYGL_DZCJJSGOCREATE TRIGGER TRIGGER_JYGL_DZCJJSON JYGL_DZCJJSAFTER INSERT, UPDATE, DELETEASDECLARE@YEAR INT,@KTXM VARCHAR(30),@PXH INT,@DH VARCHAR(50),@GQ VARCHAR(30),@WB VARCHAR(30),@GZL real,@PS int,@QTFY real,@ZJ real,@SGF real,@ZJTZ realBEGIN/* 判读是否是有效修改操作 */
IF (SELECT COUNT(*) FROM INSERTED) > 0SELECT @DH=DH FROM INSERTEDELSESELECT @DH=DH FROM DELETEDIF @DH<>'小计' AND @DH<>'合计'BEGINIF (SELECT COUNT(*) FROM INSERTED) > 0BEGINSELECT @PXH=PXH,@YEAR=ND,@KTXM=KTXM,@DH=DH,@GQ=GQ,@WB=WB FROM INSERTED/* 判断是update还是insert */IF (SELECT COUNT(*) FROM DELETED) = 0/* INSERT 需要计算排序号 */BEGIN IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计' AND PXH IS NOT NULL) > 0 SELECT TOP 1 @PXH=PXH FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计' AND PXH IS NOT NULL ELSE BEGIN IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND PXH IS NOT NULL) > 0 SELECT @PXH=ISNULL(MAX(PXH),0)+1 FROM JYGL_DZCJJS WHERE DH<>'合计' ELSE SELECT @PXH=ISNULL(MAX(PXH),0)+1 FROM JYGL_DZCJJS UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXH ENDUPDATE JYGL_DZCJJS SET PXH=@PXH WHERE ND=@YEAR AND KTXM=@KTXM AND DH=@DH AND GQ=@GQ AND WB=@WBEND/* 重新小计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计'ELSE BEGINUPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH+1,@YEAR,@KTXM,'小计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 二维合计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='二维'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='二维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='二维'ELSEBEGINSELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计'UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','二维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 三维合计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='三维'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='三维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='三维'ELSEBEGINSELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND (DH<>'合计' OR WB='二维')UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','三维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 总计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维'ELSEBEGINSELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEARINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)ENDENDELSEBEGINSELECT @PXH=PXH,@YEAR=ND,@KTXM=KTXM,@DH=DH,@GQ=GQ,@WB=WB FROM DELETED/* 判断该勘探项目是否还存在数据记录 */IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM and DH<>'小计') > 0/*该勘探项目还存在记录修改小计和合计即可*/BEGIN/* 重新小计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH<>'小计'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计'ELSE BEGINUPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH+1,@YEAR,@KTXM,'小计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 二维合计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='二维'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='二维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='二维'ELSEBEGINSELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计'UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','二维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 三维合计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='三维'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='三维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='三维'ELSEBEGINSELECT @PXH=PXH+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计' AND WB='二维'UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','三维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 总计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维'ELSEBEGINSELECT @PXH=PXH+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND WB='三维'INSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)ENDENDELSE/* 该勘探项目已经没有记录 删除小计 重新合计 */BEGINDELETE FROM JYGL_DZCJJS WHERE ND=@YEAR AND KTXM=@KTXM AND DH='小计'UPDATE JYGL_DZCJJS SET PXH=PXH-2 WHERE ND=@YEAR AND PXH>@PXHIF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计') > 0/* 重新合计 */BEGIN/* 二维合计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='二维'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='二维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='二维'ELSEBEGINSELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'合计'UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','二维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 三维合计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计' AND WB='三维'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB='三维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB='三维'ELSEBEGINSELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEAR AND (DH<>'合计' OR WB='二维')UPDATE JYGL_DZCJJS SET PXH=PXH+1 WHERE ND=@YEAR AND PXH>=@PXHINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','三维',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)END/* 总计 */SELECT @GZL=SUM(GZL),@PS=SUM(PS),@QTFY=SUM(QTFY),@ZJ=SUM(ZJ),@SGF=SUM(SGF),@ZJTZ=SUM(ZJTZ) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH<>'小计' AND DH<>'合计'IF (SELECT COUNT(*) FROM JYGL_DZCJJS WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维') > 0UPDATE JYGL_DZCJJS SET GZL=@GZL,PS=@PS,QTFY=@QTFY,ZJ=@ZJ,SGF=@SGF,ZJTZ=@ZJTZ WHERE ND=@YEAR AND DH='合计' AND WB<>'二维' AND WB<>'三维'ELSEBEGINSELECT @PXH=MAX(PXH)+1 FROM JYGL_DZCJJS WHERE ND=@YEARINSERT INTO JYGL_DZCJJS(PXH,ND,KTXM,DH,GQ,WB,GZL,PS,QTFY,ZJ,SGF,ZJTZ) VALUES(@PXH,@YEAR,'','合计','','',@GZL,@PS,@QTFY,@ZJ,@SGF,@ZJTZ)ENDENDELSEDELETE FROM JYGL_DZCJJS WHERE ND=@YEAR AND (DH='小计' OR DH='合计')ENDENDENDENDGO是值得庆幸还是一种悲哀?
