if object_id('test') is not null drop table test go create table test ([id] int identity(100001, 1), [name] varchar(20) primary key([id])) go if object_id('testtrig') is not null drop trigger testtrig go create trigger testtrig on test instead of insert as begin declare @n int select @n = max([id]) from test select identity(int, 1, 1) as [id], [name] into # from inserted insert into test([name]) select [name] from (select * from # union all select * from # where (@n + [id]) % 10 in (4, 7)) a order by [id] delete from test where [id] % 10 in (4, 7) end go insert into test([name]) select 'aaa' union select 'bbb' union select 'ccc' insert into test([name]) select 'ddd' insert into test([name]) select 'eee' union select 'fff' union select 'ggg' select * from test /* id name 100001 aaa 100002 bbb 100003 ccc 100005 ddd 100006 eee 100008 fff 100009 ggg */ drop table test
go
create table test
([id] int identity(100001, 1),
[name] varchar(20)
primary key([id]))
go
if object_id('testtrig') is not null drop trigger testtrig
go
create trigger testtrig on test instead of insert
as
begin
declare @n int
select @n = max([id]) from test
select identity(int, 1, 1) as [id], [name]
into # from inserted
insert into test([name])
select [name]
from (select * from #
union all select * from # where (@n + [id]) % 10 in (4, 7)) a
order by [id]
delete from test where [id] % 10 in (4, 7)
end
go
insert into test([name])
select 'aaa'
union select 'bbb'
union select 'ccc'
insert into test([name]) select 'ddd'
insert into test([name])
select 'eee'
union select 'fff'
union select 'ggg'
select * from test
/*
id name
100001 aaa
100002 bbb
100003 ccc
100005 ddd
100006 eee
100008 fff
100009 ggg
*/
drop table test