trigger to control table size in database

[VIJENDRA1978]

I need to control the size of table in database in Ms Sql server 2005.
My objective is to see if the table size grows beyond a certain limit like 300mb or so. If it grows more then this then fire a trigger and remove data from table in which last month entries should be removed to add more fresh data.
Now is trigger going to solve this problem.
Please provide me the code format required for this.
Or any alternative with some good link if possible.

Thanks
Vijendra

[iburyak]

See if this one would work. I had this function in Server 2000. Didn't have time to test if it works on SQL 2005. Let me know how it works, please.

Step 1.
Create function that counts approximate data size in KB.

create function spaceused (@ObjectName varchar(200), @RowNumber int)  
RETURNS int   
AS  
begin  
declare @id int, @pages int, @res int  
set @id = object_id(@ObjectName)  
 
Declare @spt_space table    
(    
 rows  int null,    
 reserved dec(15) null,    
 data  dec(15) null,    
 indexp  dec(15) null,    
 unused  dec(15) null    
)    
 
 
 insert into @spt_space (reserved)    
  select sum(reserved)    
   from sysindexes    
    where indid in (0, 1, 255)    
     and id = @id    
 
 /*    
 ** data: sum(dpages) where indid < 2    
 ** + sum(used) where indid = 255 (text)    
 */    
 select @pages = sum(dpages)    
   from sysindexes    
    where indid < 2    
     and id = @id    
 
 select @pages = @pages + isnull(sum(used), 0)    
  from sysindexes    
   where indid = 255    
    and id = @id    
 update @spt_space    
  set data = @pages    
 
 
 /* index: sum(used) where indid in (0, 1, 255) - data */    
 update @spt_space    
  set indexp = (select sum(used)    
    from sysindexes    
     where indid in (0, 1, 255)    
      and id = @id)    
       - data    
 
 /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */    
 update @spt_space    
  set unused = reserved    
    - (select sum(used)    
     from sysindexes    
      where indid in (0, 1, 255)    
       and id = @id)    
 
 update @spt_space    
  set rows = i.rows    
   from sysindexes i    
    where i.indid < 2    
     and i.id = @id    
 
IF @RowNumber = 0   
 
         select   
         @res =((data * 8192 / 1024.) + (indexp * 8192 / 1024.))  
         from @spt_space  
ELSE  
 
        select  @res = ((data * 8192 / 1024.) + (indexp * 8192 / 1024.)) * @RowNumber / rows         
        from @spt_space  
 
 
 
RETURN @res  
end  

Step 2
Write a trigger. Replace table_name, date_column with real name.

CREATE TRIGGER tI_table_name ON table_name FOR INSERT 
AS  
 
IF dbo.spaceused ('table_name', 0) > 307200
 
Delete from table_name where date_column < dateadd(m, -1, getdate()) 

Suggestion: TEST EVERYTHING FIRST!!!


NOTE: I wrote function in Step1 to count how much data I about to load in advance.
If you replace 0 with a number of rows you about to add to a table you will have space it will take for this particular table counting data + index size.
Parameter 0 will return current size of a table.

Good Luck.