向表中插入数据的时候,自动删除重复的数据

 view plaincopy to clipboardprint?
create table EE  
(  
  ID int identity(1,1) primary key,  
  bh int,  
  rq datetime,  
  sj varchar(20)  
)  
insert into EE select 1,'2009-09-09','09:01' 
insert into EE select 1,'2009-09-09','09:02' 
 
create trigger tg_Del  
on EE  
for insert  
as 
--如果表中存在的话  
if exists(select * from EE join inserted i on EE.bh=i.bh and EE.rq=i.rq and EE.sj=i.sj)  
--删除刚刚插入的信息  
delete E1 from EE E1,inserted i where E1.bh=i.bh and E1.rq=i.rq and E1.sj=i.sj  
and i.ID>E1.ID  
--测试数据  
insert into EE(bh,rq,sj) select 1,'2009-09-09','09:02' 
select * from EE  
ID          bh          rq                      sj  
----------- ----------- ----------------------- --------------------  
1           1           2009-09-09 00:00:00.000 09:01  
3           1           2009-09-09 00:00:00.000 09:02  
 
insert into EE(bh,rq,sj) select 1,'2009-09-09','09:03' 
 
select * from EE  
 
ID          bh          rq                      sj  
----------- ----------- ----------------------- --------------------  
1           1           2009-09-09 00:00:00.000 09:01  
3           1           2009-09-09 00:00:00.000 09:02  
4           1           2009-09-09 00:00:00.000 09:03 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/11/02/4757860.aspx