再论触发器

Question

/*
数据表:ContractDetail
ccode  irowNO   cinvname
001      1         A                                                           
001      2         B  
001      3         D
002      1         A
002      2         B 
*/要求的触发器是:
插入记录前检查ccode+irowNO是否有相同的记录,如果有相同的,irowNO的值则用按ccode字段分组后的MAX(irowNO)+1代替再插入资料示例如下:
插入新记录
002   2  C 因为资料已存在ccode+irowNO记录,则要变成这样:
ccode  irowNO   cinvname
001      1         A                                                           
001      2         B  
001      3         D
002      1         A
002      2         B 
002      3         C
测试用的数据建表脚本:http://topic.csdn.net/u/20100529/11/c8700e22-db1e-4314-b48a-8b3fa736f660.html?1137858076

Answer 1

就是说你的数据在插入表之前就会有重复的，这个用一句insert一定是不能处理了

Answer 2

感谢您了:xys_777,如果用临时来替代游标,你认为效果是不是会更好呢?

Answer 3

临时表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContractDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ContractDetail]
GO
CREATE TABLE [dbo].[ContractDetail] (
    [cCode] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [iRowNo] [int] NOT NULL ,
    [cInvName] [char] (60) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOINSERT INTO ContractDetail
select '001',1,'A'
UNION ALL
select '001',2,'B'
UNION ALL
select '001',3,'D'
UNION ALL
select '002',1,'A'
UNION ALL
select '002',2,'B'  
go--触发器
create trigger tri_con_insert
on ContractDetail
instead of insert
as
begin
  select *,tid=identity(int,1,1) into # from inserted;
  insert ContractDetail
  select a.cCode,isnull(b.maxrow,0)+a.px,a.cInvName
  from
   (select *,px=(select count(1)+1 
    from # 
    where cCode=t.cCode and tid<t.tid) 
    from # t
   ) a
    left join
   (select ccode,max(irowno) as maxrow from ContractDetail group by ccode) b
   on a.ccode=b.ccode
end
go--插入数据
INSERT INTO ContractDetail
select '005',1,'0091001'
UNION ALL
select '003',1,'000001c'
UNION ALL
select '003',1,'000002c'
UNION ALL
select '003',1,'000003c'
UNION ALL
select '003',1,'000004c'
UNION ALL
select '003',1,'000005c'
UNION ALL
select '003',1,'000006c'
UNION ALL
select '001',1,'000001c'--查询
select * from ContractDetail--结果
/**
cCode                iRowNo      cInvName
-------------------- ----------- ------------------------------------------------------------
001                  1           A                                                           
001                  2           B                                                           
001                  3           D                                                           
002                  1           A                                                           
002                  2           B                                                           
005                  1           0091001                                                     
003                  1           000001c                                                     
003                  2           000002c                                                     
003                  3           000003c                                                     
003                  4           000004c                                                     
003                  5           000005c                                                     
003                  6           000006c                                                     
001                  4           000001c                                                     (13 行受影响)**/