消息 547，级别 16，状态 0，过程 tr_XZYBIn，第 35 行 INSERT 语句与 COLUMN FOREIGN KEY 约束 'XZYB_Tow

a375941852 2012-04-27 09:31:41
USE [web_stat]
GO

ALTER TRIGGER [dbo].[tr_XZYBIn] ON [dbo].[XZYB]
INSTEAD OF INSERT
AS
BEGIN
DECLARE @year int,@month int,@type int,
@sj1 float,@sj2 float,@sj3 float,@sj4 float,
@sj5 float,@sj6 float,@sj7 float,@sj8 float,
@sj9 float,@sj10 float,@sj11 float,@sj12 float,
@sj13 float,@sj14 float,@sj15 float,@town_CCodeID varchar(3),
--(本期)将表XZYB中新插入的数据记录放入insert临时表
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
IF not exists(select * from XZYB where Year=@year and Month=@month and Type=@type and Town_CCodeID=@town_CCodeID)
BEGIN



SELECT @sj1=CCP,
@sj2=CKCPJHZ,@sj3=KSE,@sj4=KSQYS,@sj5=LDZBC,
@sj6=LDZCHJ,@sj7=LRZE,@sj8=LSZE,@sj9=QMQYS,
@sj10=SJSJZE,@sj11=XJXSCZ,@sj12=XJZCZ,
@sj13=YSZKJE,@sj14=YYSR,@sj15=ZJZ,@town_CCodeID=Town_CCodeID
FROM inserted


BEGIN

INSERT into XZYB
SELECT Year,Month,Town_CCodeID,Type,CCP,CKCPJHZ,KSE,KSQYS,
LDZBC,LDZCHJ,LRZE,LSZE,QMQYS,SJSJZE,XJXSCZ,XJZCZ,
YSZKJE,YYSR,ZJZ,CCP_T,CKCPJHZ_T,KSE_T,KSQYS_T,LDZBC_T,
LDZCHJ_T,LRZE_T,LSZE_T,QMQYS_T,SJSJZE_T,XJXSCZ_T,XJZCZ_T,
YSZKJE_T,YYSR_T,ZJZ_T
FROM inserted

END

...全文
434 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
EnForGrass 2012-04-27
  • 打赏
  • 举报
 回复
你应该先给主键的表插入数据
a375941852 2012-04-27
  • 打赏
  • 举报
 回复
--------------------------------当@type为-6时


BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP,
@sj2=CKCPJHZ,@sj3=KSE,@sj4=KSQYS,@sj5=LDZBC,
@sj6=LDZCHJ,@sj7=LRZE,@sj8=LSZE,@sj9=QMQYS,
@sj10=SJSJZE,@sj11=XJXSCZ,@sj12=XJZCZ,
@sj13=YSZKJE,@sj14=YYSR,@sj15=ZJZ,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-6'

BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+11)
BEGIN
--XZYB表对NTYB表的插入更新
--从insert临时表中取出相关数据插入表NTYB
--本期
INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,@type+11,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')
END
ELSE
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,
LSZE=@sj8,HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,
GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+11

END
END
END





--同期
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP_T,
@sj2=CKCPJHZ_T,@sj3=KSE_T,@sj4=KSQYS_T,@sj5=LDZBC_T,
@sj6=LDZCHJ_T,@sj7=LRZE_T,@sj8=LSZE_T,@sj9=QMQYS_T,
@sj10=SJSJZE_T,@sj11=XJXSCZ_T,@sj12=XJZCZ_T,
@sj13=YSZKJE_T,@sj14=YYSR_T,@sj15=ZJZ_T,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-1'

BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+7)
BEGIN
INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,@type+7,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')

END
ELSE
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,
LSZE=@sj8,HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,
GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+7

END
END
END






--同期
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP_T,
@sj2=CKCPJHZ_T,@sj3=KSE_T,@sj4=KSQYS_T,@sj5=LDZBC_T,
@sj6=LDZCHJ_T,@sj7=LRZE_T,@sj8=LSZE_T,@sj9=QMQYS_T,
@sj10=SJSJZE_T,@sj11=XJXSCZ_T,@sj12=XJZCZ_T,
@sj13=YSZKJE_T,@sj14=YYSR_T,@sj15=ZJZ_T,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-3'

BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+10)
BEGIN
INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,@type+10,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')

END
ELSE
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,
LSZE=@sj8,HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,
GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+10

END
END
END










--同期
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP_T,
@sj2=CKCPJHZ_T,@sj3=KSE_T,@sj4=KSQYS_T,@sj5=LDZBC_T,
@sj6=LDZCHJ_T,@sj7=LRZE_T,@sj8=LSZE_T,@sj9=QMQYS_T,
@sj10=SJSJZE_T,@sj11=XJXSCZ_T,@sj12=XJZCZ_T,
@sj13=YSZKJE_T,@sj14=YYSR_T,@sj15=ZJZ_T,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-4'

BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+12)
BEGIN
INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,@type+12,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')

END
ELSE
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,
LSZE=@sj8,HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,
GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+12

END
END
END









a375941852 2012-04-27
  • 打赏
  • 举报
 回复
--------------------------------当@type为-4时




BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP,
@sj2=CKCPJHZ,@sj3=KSE,@sj4=KSQYS,@sj5=LDZBC,
@sj6=LDZCHJ,@sj7=LRZE,@sj8=LSZE,@sj9=QMQYS,
@sj10=SJSJZE,@sj11=XJXSCZ,@sj12=XJZCZ,
@sj13=YSZKJE,@sj14=YYSR,@sj15=ZJZ,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-4'

BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+7)
BEGIN
--XZYB表对NTYB表的插入更新
--从insert临时表中取出相关数据插入表NTYB
--本期

INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,@type+7,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')

END
ELSE
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,
LSZE=@sj8,HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,
GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+7

END
END
END






--------------------------------当@type为-5时


BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP,
@sj2=CKCPJHZ,@sj3=KSE,@sj4=KSQYS,@sj5=LDZBC,
@sj6=LDZCHJ,@sj7=LRZE,@sj8=LSZE,@sj9=QMQYS,
@sj10=SJSJZE,@sj11=XJXSCZ,@sj12=XJZCZ,
@sj13=YSZKJE,@sj14=YYSR,@sj15=ZJZ,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-5'

BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+9)
BEGIN
--XZYB表对NTYB表的插入更新
--从insert临时表中取出相关数据插入表NTYB
--本期
INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,@type+9,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')
END
ELSE
BEGIN

SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,
LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,LSZE=@sj8,
HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,
YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+9

END
END
END





a375941852 2012-04-27
  • 打赏
  • 举报
 回复
BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP,
@sj2=CKCPJHZ,@sj3=KSE,@sj4=KSQYS,@sj5=LDZBC,
@sj6=LDZCHJ,@sj7=LRZE,@sj8=LSZE,@sj9=QMQYS,
@sj10=SJSJZE,@sj11=XJXSCZ,@sj12=XJZCZ,
@sj13=YSZKJE,@sj14=YYSR,@sj15=ZJZ,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-1'

BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=-@type)
BEGIN

INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,-@type,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')

END
ELSE
BEGIN

SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,
LSZE=@sj8,HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,
GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=-@type
END
END
END




--------------------------------当@type为-3时


BEGIN
SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM inserted
SELECT @sj1=CCP,
@sj2=CKCPJHZ,@sj3=KSE,@sj4=KSQYS,@sj5=LDZBC,
@sj6=LDZCHJ,@sj7=LRZE,@sj8=LSZE,@sj9=QMQYS,
@sj10=SJSJZE,@sj11=XJXSCZ,@sj12=XJZCZ,
@sj13=YSZKJE,@sj14=YYSR,@sj15=ZJZ,@town_CCodeID=Town_CCodeID
FROM inserted
IF @type='-3'
BEGIN
IF not exists(select * from NTYB where Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+5)
BEGIN
--XZYB表对NTYB表的插入更新
--从insert临时表中取出相关数据插入表NTYB
--本期

INSERT into NTYB (Year,Month,Town_CCodeID,Type,CCP,CKCZJHZ,KSQYKSE,KSQYS,LDZBC,LDZCHJ,LRZE,
LSZE,HZQYS,SJSJ,XJXSCZ,XJZCZ,YSKX,YYSR,ZJZ,
GDZCTZE,QMCYRS)
VALUES (@year,@month,@town_CCodeID,@type+5,@sj1,@sj2,@sj3,@sj4,
@sj5,@sj6,@sj7,@sj8,@sj9,@sj10,@sj11,@sj12,@sj13,@sj14,
@sj15,'0','0')

END
ELSE
BEGIN

SELECT @year=Year,@month=Month,@type=Type,@town_CCodeID=Town_CCodeID
FROM deleted
UPDATE NTYB
SET CCP=@sj1,CKCZJHZ=@sj2,KSQYKSE=@sj3,KSQYS=@sj4,LDZBC=@sj5,LDZCHJ=@sj6,LRZE=@sj7,
LSZE=@sj8,HZQYS=@sj9,SJSJ=@sj10,XJXSCZ=@sj11,XJZCZ=@sj12,YSKX=@sj13,YYSR=@sj14,ZJZ=@sj15,
GDZCTZE='0',QMCYRS='0'
WHERE Year=@year and Month=@month and Town_CCodeID=@town_CCodeID and Type=@type+5

END
END
END





尼古拉特斯拉 2012-04-27
  • 打赏
  • 举报
 回复
主键冲突吧
[ Node.js ] ER_BAD_FIELD_ERROR: Unknown column '(你提交的字段)' in 'field list'
params.body.nickname
Column ‘XXX‘ cannot be null问题 后端接收不到前端表单提交的数据
前景摘要：整合SSM练习Insert语句时，想通过前端页面的表单将数据通过后端的处理存储到数据库，但是一直报Column ‘列名‘ cannot be null（某个列名不能为空）的错误，急着知道的直接看最后，中间都是牢骚。 查询功能能够正常运行，但就是插入用户功能报错，如下图 代码逻辑没有问题 Controller层保存用户方法代码 @RequestMapping("/save") public void save(User user, HttpServletRequest request,
itoa、_itoa、ltoa、_ltoa、ultoa、_ultoa、_i64toa、_ui64toa、_itow、_ltow、_ultow、_i64tow、_ui64tow (转载)
文章目录前言语法参数value宽限基数 radix大小 size返回值备注重要最大转换计数宏一般文本例程映射要求示例原文网址 前言 将整数转换为字符串。 提供这些函数的更多安全版本，请参阅 _itoa_s、_itow_s 函数。 语法 char * _itoa( int value, char *buffer, int radix ); char * _ltoa( long value, char *buffer, int radix ); char * _ultoa( unsigned long v
经典hibernate配置详解
元素的属性 属性 描述 class-description 指定描述类的javaDoc field-description 指定描述类的属性javaDoc interface 如果为true,表明生成接口而非类,默认false implements 指定类所实现的接口 extends 指定继承的父类名 generated-class 重新指定生成的类名 scope-class
【Teradata】Latin和Unicode字符集转换（自定义函数udf_w936to16和udf16tow936）
1.安装UDF函数 （1）下载GBK2Unicode.zip，地址为https://downloads.teradata.com/tag/udf （2）windows或者Linux环境下使用bteq进行安装。 进入文件所在目录，输入如下bteq命令登录数据库（windows中使用dos） bteq .logon 192.168.253.222/dbc,dbc; 输入如下命令安装U...
.NET社区

62,268

社区成员

668,981

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见，支持健康理性的辩论和互动，反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧

+ 用AI写文章