SQL触发器

[yyhakusu]

各位好!
小弟想写一个触发器实现如下功能:
表名:data
字段:num,sign
当表中的记录条数>=200的时候,把sign=1的前100条记录拷贝到signdata表中,并在data表中删除这100条记录.
这个功能应该怎么实现呢?谢谢各位大虾!~~~

[-狙击手-]

create trigger triggername
on data
for insert
as
begin
select 1 from data
if @@rowcount >= 200
begin
insert signdata select top 100 * from data order by id
delete from data where exists(select 1 from signdata)
end
end

[kk19840210]

create trigger t_date on date
for insert
as
begin
	if (select count(1) from date) >=200
		begin
			insert into signdata
				select top 100 * from date where [sign]=1
			delete from date where num in (select top 100 num from date where [sign]=1)
		end
end

[冷箫轻笛]

create table data
(
id int,
aaa varchar(10),
[sign] int
)


create trigger t_1 on [data]
for insert
as
begin
declare @count int
select @count = count(1) from [data]
if @count >= 200
begin
insert into signdata(id,aaa,[sign])
select top 100 id,aaa,[sign]
from data
where [sign] = 1

set rowcount 100
delete from data where [sign] = 1
set rowcount 0
end
end


好久没有写sql了，写起来有点生疏
没有测试，楼主自己测一下吧

[冷箫轻笛]

create table data
(
id int,
aaa varchar(10),
[sign] int
)


create trigger t_1 on [data]
for insert
as
begin
declare @count int
select @count = count(1) from [data]
if @count >= 200
	begin
		insert into signdata(id,aaa,[sign])
		select top 100 id,aaa,[sign]
		from data
		where [sign] = 1
		
		set rowcount 100
		delete from data where [sign] = 1
		set rowcount 0
	end
end

呵呵，抢分的速度也慢了好多
这样看起来会好一些？

[yyhakusu]

多谢各位~~问题解决了!! 一会加分,嘿嘿!~