当数据达到一万条记录时,如果用存储过程来限制插入数据?

[suny2003]

当数据达到一万条记录时,如果用存储过程来限制插入数据?

[yrwx001]

寫觸發器
create trigger trg_a on table a after insert
as
declare @icount int
select @icount = count(1) from a
if @icount > 10000
begin
declare @key varchar(30)
select @key = Key from inserted
delete a where Key = @key
end

[smaworm]

楼上正解，存储过程是需要调用的，你说的情况触发器更合适。

[suny2003]

create PROCEDURE addTableA
@a int,
@b int,
@c int
as
declare @num as varchar(30)
insert tableA (a,b,c) values(@a,@b,@c)
@num=@@identity
if(@num>=10000)
delete from tableA where id>=@num

不知道这样写有没问题?

[comszsoft]

触发器，我也写个，借用一楼代码

create trigger trg_a on table a after insert
as
declare @icount int
select @icount = rows from sysindexes where id= object_id('a') -- 就这里有些不同
if @icount > 10000
begin
declare @key varchar(30)
select @key = Key from inserted
delete a where Key = @key
end

[comszsoft]

上面错了，应该这个
create trigger tr_userinfo_insert on 表名
for insert
as
declare @count int
select @count=rows from sysindexes where id=object_id('表名') and indid < 2
if(@count >= 10000)
begin
delete from table1 where 主键= (select 主键 from inserted)
end