建立触发器
create trigger tr_test on tablename
for insert
as
declare @count int
select @count=count(*) from tablename
if @count>5000
delete from tablename where ...
create trigger tr_test on tablename
for insert
as
declare @count int
select @count=count(*) from tablename
if @count>5000
delete from tablename where ...
用JOB。添加一个作业,
定时运行代码:delect from 表 where id not in (select top 5000 id from 表 order by id DESC)
For Insert
As
declare @n int
select @n = count(*) from inserted
exec ('delete yourTable where id in (select top ' + @n + ' id from yourTable)')
for insert
as
delect from 表 where id not in (select top 5000 id from 表 order by id DESC)
for insert
as
delect from 表 where id not in (select top 5000 id from 表 order by id DESC)
----
ID可以是一个自增列,也可以是你的表的主键.
create trigger t_name on tableName
for insert
as
delete from tableName where 时间字段 not in (select top 5000 时间字段 from tableName order by 时间字段 desc
for insert
as
declare @count int
select @count=count(*) from tablename
if @count>5000
delete from tablename where id not in (select top 5000 id from tablename order by id desc)
create trigger Tr_DelTable
on history for insert
as
begin
declare @countn as int
select @countn=count(*) from history
while @countn<=5000
begin
select identity(int,1,1) as idd,* into #temp from history
delete from #t where idd=(select min(idd) from #temp)
select @countn=count(*) from history
end
end
for insert
as
if (select sum(1) from 表)>5000
delete 表 where 编号 not in (select top 5000 编号 from 表 order by 时间或编号 desc)
使用的语句如上几位大侠所言