CREATE TRIGGER Trigger_TelWav
ON [dbo].[TelWav]
for INSERT
AS
declare @TempSize int
select @TempSize=size*8/1024 from sysfiles where fileid=1
select @TempSize
if @TempSize>1900 then
delete from telwav where exists(
select top 10 percent * from telwav b where telwav.Calltime=b.CallTime)
dbcc shrinkfile(1)
dbcc shrinkfile(2)
end
ON [dbo].[TelWav]
for INSERT
AS
declare @TempSize int
select @TempSize=size*8/1024 from sysfiles where fileid=1
select @TempSize
if @TempSize>1900 then
delete from telwav where exists(
select top 10 percent * from telwav b where telwav.Calltime=b.CallTime)
dbcc shrinkfile(1)
dbcc shrinkfile(2)
end
delete from telwav where exists(
select top 10 percent * from telwav b where telwav.Calltime=b.CallTime)
TelWav表是一个没有主键的表,我需要删除这个表的10%的记录,那么请问你,该怎么写呢?
from
telwav a
where
a.CallTime in(select top 10 percent Calltime from telwav order by Calltime)
(select top 10 percent * from telwav) b where telwav.Calltime=b.CallTime)
谢谢你们,试过了,都可以的,
但是把你们写的删除语句放进去,那个触发器还是有问题,请你们再看看好吗?
ON [dbo].[TelWav]
for INSERT
AS
BEGIN
declare @TempSize int
select @TempSize=size*8/1024 from sysfiles where fileid=1
select @TempSize
IF @TempSize>1900
BEGIN
delete a
from
telwav a
where
a.CallTime in(select top 10 percent Calltime from telwav order by Calltime)
dbcc shrinkfile(1)
dbcc shrinkfile(2)
END
END
GO
where a.CallTime in(select top 10 percent Calltime from telwav order by Calltime)
这个触发器我是用解决这个的:
当数据库的容量超过1.9G时,删除表Telwav(录音文件)表中10%的记录
(说明: Telwav这个表没有主键,存在重复的数据)
这是一个msde 2000的数据库,删除数据之后,数据库还不能进行收缩,所以在进行删除后得收缩数据库想问你是否还有更好的解决方法?
那请问要如何在后台进程来进行收缩操作呢