首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

触发器更新另一个表中多列的值 为啥老出错 帮忙求解

2012-07-28 
触发器更新另一个表中多列的值 为何老出错 帮忙求解我想在DDSH表的触发器中设置当其中的某几个字段(都为IN

触发器更新另一个表中多列的值 为何老出错 帮忙求解
我想在DDSH表的触发器中设置当其中的某几个字段(都为INT)都为6的时候 更新SCRWTZD中相识DDH的SH(逻辑)值为1 否则为0(可能有好几行)
可是目前似乎触发器不起作用 求解释

CREATE TRIGGER SHDD ON [dbo].[DDSH] 
FOR INSERT,UPDATE
AS
begin
  declare @DDH nchar
  declare @YW int,@WL int,@PZ int,@JL int,@SC int,@GC int
  select @DDH = [DDH] from inserted
  select @YW = [YWJL] from inserted
  select @WL = [WLB] from inserted
  select @PZ = [PZB] from inserted
  select @JL = [ZJL] from inserted
  select @SC = [SCB] from inserted
  select @GC = [GCJSB] from inserted
  if (@YW = 6) and (@WL = 6) and (@pz = 6) and (@JL = 6) and (@SC = 6) and ( @GC = 6)
  update [SCRWTZD] set [SH] = 1 where [DDH] = @DDH
  else 
  update [SCRWTZD] set [SH] = 0 where [DDH] = @DDH
end

[解决办法]

SQL code
CREATE TRIGGER SHDD ON [dbo].[DDSH]  FOR INSERT,UPDATEASbegin  update [SCRWTZD] set [SH] = 1  where [DDH] in (    select [DDH] from inserted where [YWJL] = 6 and [WLB] = 6 and [PZB] = 6 and [ZJL] = 6 and [SCB] = 6 and [GCJSB] = 6  )    update [SCRWTZD] set [SH] = 0  where [DDH] in (    select [DDH] from inserted where [YWJL] <> 6 or [WLB] <> 6 or [PZB] <> 6 or [ZJL] <> 6 or [SCB] <> 6 or [GCJSB] <> 6  )  end
[解决办法]
SQL code
CREATE TRIGGER SHDD ON [dbo].[DDSH]  FOR INSERT,UPDATEASbegin    update [SCRWTZD] set [SH] = case when i.[DDH]=6 and i.[YWJL]=6 and ....  then 1 else 0 end from inserted i where i.[DDH]=[SCRWTZD].[DDH]  end --你的更新只能更新一行数据
[解决办法]
declare @DDH nchar 后面没有定义 nchar为几位的话,默认为1。
假如字长为10,那么需要这样定义:nchar(10)

[解决办法]
SQL code
CREATE TRIGGER SHDD ON [dbo].[DDSH]  FOR INSERT,UPDATEASbegin  update [SCRWTZD]         set [SH]=1         from inserted i,[SCRWTZD] s where i.[DDH]=s.[DDH]             and i.[YWJL]=6 and i.[WLB]=6 and i.[PZB]=6             and i.[ZJL]=6 and i.[SCB]=6 and i.[GCJSB]=6;  update [SCRWTZD]         set [SH]=0        from inserted i,[SCRWTZD] s where i.[DDH]=s.[DDH]             and (isnull(i.[YWJL],0)<>6 or isnull(i.[WLB],0)<>6 or isnull(i.[PZB],0)<>6            or isnull(i.[ZJL],0)<>6 or isnull(i.[SCB],0)<>6 or isnull(i.[GCJSB],0)<>6);end 

热点排行