编程论坛 → SQL Server论坛

建立触发器后，不能像表中插入数据，是什么原因

jinzhengbe 发布于 2017-03-31 14:26， 7665 次点击

USE [cai2016]
GO
/****** Object:  Trigger [dbo].[trigger_yunum]    Script Date: 2017/3/29 17:30:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter trigger [dbo].[trigger_yunum]
　　on [dbo].[newmoo_case_cgxd]
　  for  insert
　　as
    BEGIN

    DECLARE @id bigint SELECT @id = id FROM INSERTED
    DECLARE @purchase_id varchar SELECT @purchase_id = purchase_id FROM INSERTED
    DECLARE @slength nvarchar SELECT @slength = slength FROM INSERTED
    DECLARE @engname nvarchar SELECT @engname = engname FROM INSERTED

    DECLARE @swidth nvarchar SELECT @swidth = swidth FROM INSERTED
    DECLARE @kehu_id bigint SELECT @kehu_id = kehu_id FROM INSERTED
    DECLARE @caseid bigint select @caseid =caseid FROM INSERTED
    DECLARE @productnum int SELECT @productnum = productnum FROM INSERTED
    DECLARE @pay numeric SELECT @pay = pay FROM INSERTED
    DECLARE @costprice numeric SELECT @costprice = costprice FROM INSERTED

    DECLARE @pid_p bigint select @pid_p =pid_p FROM INSERTED
    DECLARE @cz int SELECT @cz = cz FROM INSERTED
    DECLARE @productRemarks nvarchar SELECT @productremarks = productremarks FROM INSERTED
    DECLARE @weight numeric SELECT @weight = weight FROM INSERTED

    Update  a set a.overnum=(select a.id, a.overnum, a.oknum, a.yunum, b.caseid, b.productnum, b.ischeck, sum(b.productnum)  from newmoo_case a, newmoo_case_cgxd b where b.caseid=a.id and b.ischeck=3)


end

请问这个触发器设计的有问题么？
建立之后表b就不能插入数据了啊
删除触发器后就能恢复正常。

8 回复

#2

mywisdom882017-03-31 16:46

use test
go
-- 表1(id,productnum),建立几个测试数据
if object_id('表1') is null
begin
  create table 表1(id int,productnum numeric(10,2))
  insert into 表1
  select 1001,10.5 union all
  select 1001,20.5 union all
  select 1001,30.5 union all
  select 1001,30.5 union all
  select 1002,20.2 union all
  select 1002,30.2
end
-- select * from 表1

-- 表2(id,overnum,upd),建立几个测试数据，汇总表
if object_id('表2') is null
begin
  create table 表2(id int,overnum numeric(10,2),upd datetime )
  insert into 表2
  select 1001,0.0,getdate() union all
  select 1002,0.0,getdate()
end
-- select * from 表2
go

-- 上面的是测试数据，下面开始建立触发器

alter trigger [dbo].[tr_表1] --第1次运行时，create,
on [dbo].[表1]
for insert
as
begin
  declare @id int
  select @id=id from inserted

  update a set a.overnum=b.productnum,upd=getdate()
  from 表2 a,(select id,productnum=sum(productnum) from 表1 where id=@id group by id) b
  where a.id=b.id
end

go

-- 测试往“表1”插入数据，看表2是否更新,查看前后变化
select * from 表2
go
insert into 表1(id,productnum) values(1002,30.5)
select * from 表2

只有本站会员才能查看附件，请登录

#3

mywisdom882017-03-31 16:47

-- 测试往“表1”插入数据，看表2是否更新,查看前后变化
select * from 表2
go
insert into 表1(id,productnum) values(1001,20.5)
select * from 表2

只有本站会员才能查看附件，请登录

#4

mywisdom882017-03-31 16:58

上面的，只更新表2存在的记录的，如果表2不存在，是不更新的，如
insert into 表1(id,productnum) values(1003,100)
表2就更新不了。
把上面的触发器修改1下，就可以
alter trigger [dbo].[tr_表1] --第1次运行时，create,
on [dbo].[表1]
for insert
as
begin
  declare @id int
  select @id=id from inserted
  if exists(select id from 表2 where id=@id)
     begin
       update a set a.overnum=b.productnum,upd=getdate()
       from 表2 a,(select id,productnum=sum(productnum) from 表1 where id=@id group by id) b
       where a.id=b.id
     end
  else
     begin
       insert into 表2(id,overnum,upd)
       select id,productnum,getdate() from inserted
     end
end

go

-- 测试往“表1”插入数据，看表2是否更新,查看前后变化
select * from 表2
go
insert into 表1(id,productnum) values(1003,20.5)
select * from 表2

只有本站会员才能查看附件，请登录

#5

jinzhengbe2017-04-02 09:54

非常感谢版主的热心回答。竟然写这么仔细，感动了~~~~

#6

jinzhengbe2017-04-02 11:14

alter trigger [dbo].[newmoo_case_cgxd] --第1次运行时，create,
on [dbo].[newmoo_case_cgxd]
for insert
as
begin
  declare @caseid bigint
  select @caseid = caseid from inserted

  if exists(select id from newmoo_case where id=@caseid)
     begin
       update a set a.overnum=b.productnum,end_date=getdate()
       from newmoo_case a,(select caseid,productnum=sum(productnum) from newmoo_case_cgxd where caseid=@caseid group by id) b
       where a.id=b.caseid
     end
  else
     begin
       insert into newmoo_case(id,overnum,end_date)
       select caseid,productnum,getdate() from inserted
     end
end

版主，我按照你的思路改了一下。但是还是有错误。
您能帮忙在指点一下么？
newmoo_case 是要更新的表
newmoo_case_cgxd是要插入数据的表， case表的id 和 case_cgxd表的caseid 是关联的。（）

消息 8120，级别 16，状态 1，过程 newmoo_case_cgxd，第 13 行
选择列表中的列 'newmoo_case_cgxd.caseid' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

#7

mywisdom882017-04-02 11:29

from newmoo_case a,(select caseid,productnum=sum(productnum) from newmoo_case_cgxd where caseid=@caseid group by id) b

group by id 改为 group by caseid

#8

jinzhengbe2017-04-02 14:46

alter trigger aaacgxd --第1次运行时，create,
on [dbo].[newmoo_case_cgxd]
for insert
as
begin
  declare @caseid bigint
  declare @ischeck int
  select @caseid = caseid from inserted
  select @ischeck = ischeck from inserted

  if exists(select id from newmoo_case where id=@caseid)
     begin
       update a set a.overnum=b.productnum,end_date=getdate()
       from newmoo_case a,(select caseid,productnum=sum(productnum) from newmoo_case_cgxd  group by caseid) b
       where a.id=b.caseid and ischeck= '3'
     end
  else
     begin
       insert into newmoo_case(id,overnum,end_date)
       select caseid,productnum,getdate() from inserted
     end
end

版主前辈，经过您的指点和我的测试，代码已经改成上面的样子了。
但是结果还是有差异。下面有附图，
我要的结果是 a.id=b.caseid 同时 ischeck= '3'
但是测试结果是，如果 ischeck包含3，就会合计所有相同id的 productnum (合计内容包含 ischeck 不能3的）
我要的是只合计 ischeck=3的
还请您在指点一下非常感谢

[local]1[/local]

[此贴子已经被作者于2017-4-2 14:48编辑过]

#9

jinzhengbe2017-04-02 20:09

已经解决了。谢谢版主前辈

1