IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int ,name1 varchar(10),name2 varchar(10), mydate datetime )
go
create trigger ko
on tb
instead of insert
as
begin
insert tb
select *
from inserted i
where id=(select top 1 id from inserted where name1=i.name1 and name2=i.name2 order by id)
end
go利用下面这样的语句是可以防止重复
insert into tb select
1,1 ,1, '2008-09-10' union all select
2,1 , 1,'2008-09-02' union all select
3,1 , 1, '2008-10-10'
go
insert into tb select
4,2 , 2, '2009-10-09' union all select
5,2 , 2,'2009-10-10'
go
insert into tb select
6,1 , 2, '2007-12-12' union all select
7,1 , 2,'2007-10-09'
但是利用insert into tb values(1,1,1'2009-12-12')这样的语句绝对不行的.不知道为什么?
DROP TABLE tb
GO
CREATE TABLE tb(id int ,name1 varchar(10),name2 varchar(10), mydate datetime )
go
create trigger ko
on tb
instead of insert
as
begin
insert tb
select *
from inserted i
where id=(select top 1 id from inserted where name1=i.name1 and name2=i.name2 order by id)
end
go利用下面这样的语句是可以防止重复
insert into tb select
1,1 ,1, '2008-09-10' union all select
2,1 , 1,'2008-09-02' union all select
3,1 , 1, '2008-10-10'
go
insert into tb select
4,2 , 2, '2009-10-09' union all select
5,2 , 2,'2009-10-10'
go
insert into tb select
6,1 , 2, '2007-12-12' union all select
7,1 , 2,'2007-10-09'
但是利用insert into tb values(1,1,1'2009-12-12')这样的语句绝对不行的.不知道为什么?
兄弟前面多个一
insert into tb values(1,1,'2009-12-12')
DROP TABLE tb
GO
CREATE TABLE tb(id int ,name1 varchar(10),name2 varchar(10), mydate datetime )
go
create trigger ko
on tb
instead of insert
as
begin
insert tb
select *
from inserted i
where not exists (select * from tb where name1=i.name1 and name2=i.name2 and id<i.id)
end
go
insert into tb values(1,1,1,'2009-12-12')
insert into tb values(2,1,1,'2008-09-02')
insert into tb values(3,1,1, '2008-10-10')select * from tb
id name1 name2 mydate
----------- ---------- ---------- -----------------------
1 1 1 2009-12-12 00:00:00.000
insert into tb
SELECT 1,1,1,'2009-12-12' UNION ALL
SELECT 2,1,1,'2008-09-02' UNION ALL
SELECT 3,1,1, '2008-10-10'
on tb
for insert
as
begin
if exists(select 1 from tb t join inserted s on t.name1=s.name1 and s.name2=t.name2)
rollback
go
DROP TABLE tb
GO
CREATE TABLE tb(id int identity(1,1) ,name1 varchar(10),name2 varchar(10), mydate datetime )
go
create trigger ko
on tb
instead of insert
as
begin insert tb(name1,name2,mydate)
select name1,name2,mydate
from inserted i
where not exists (select * from tb where name1=i.name1 and name2=i.name2 and id>i.id)
end
go
insert into tb values(1,1,'2009-12-12')
insert into tb values(1,1,'2008-09-02')
insert into tb values(1,1, '2008-10-10')select * from tb
/*
id name1 name2 mydate
----------- ---------- ---------- -----------------------
1 1 1 2009-12-12 00:00:00.000
*/
DROP TABLE tb
GO
CREATE TABLE tb(id int identity(1,1) ,name1 varchar(10),name2 varchar(10), mydate datetime )
go
create trigger ko
on tb
instead of insert
as
begin insert tb(name1,name2,mydate)
select name1,name2,mydate
from inserted i
where not exists (select * from tb where name1=i.name1 and name2=i.name2 and id>i.id)
end
go
insert into tb values(1,1,'2009-12-12')
insert into tb values(1,1,'2008-09-02')
insert into tb values(1,1, '2008-10-10')insert into tb values(2 , 2, '2009-10-09' )
insert into tb values(2 , 2,'2009-10-10' )
insert into tb values(1 , 2, '2007-12-12' )
insert into tb values(1 , 2,'2007-10-09' )select * from tb
/*
id name1 name2 mydate
----------- ---------- ---------- -----------------------
1 1 1 2009-12-12 00:00:00.000
2 2 2 2009-10-09 00:00:00.000
3 1 2 2007-12-12 00:00:00.000
*/
insert tb(...) select ... where (select top 1 ... from tb order by 自增字段 desc) <> ...
on tb
instead of insert
as
begin
insert tb
select *
from inserted i
where not exists (select 1 from tb where name1=i.name1 and name2=i.name2)---加这一句话
and id=(select top 1 id from inserted where name1=i.name1 and name2=i.name2 order by id)
end