Is there a way to have a trigger fire when a table hits a specified size?
I am doubtful, but I thought I would ask. It would be useful for logging tables that I want to keep under a specific size. (I would delete older entries when the trigger fired.)
I think this problem is much better served using a scheduled SSIS maintenance package, but if you really wanted to do it this way, you could add a trigger that would fire on insert into the table that would then calculate the tables size and then perfomr the action that you want.
If you absolutely wanted this to be triggered, I would only have the trigger just queue an action in a table which was monitored by a maintenance job, since a trigger should not, in general, perform extended operations - they should get there stuff done and return control so that the transaction can complete or fail as quickly as possible. Note that there are situations where triggers have gotten disabled and you would not be able to have your maintenance run. If someone drops your trigger it might not be as obvious as a job which isn't showing up on a regular report.
I would recommend a job run by the SQL Server Agent regularly looking for situations where it needs to take action performing the actions and reporting them appropriately to whatever reports or system management software you are using.
Not sure if this will help, but you can set an agent alert on cumulative size of files in a database. Under SQL Server Agent/Alerts/Sql Server Performance Condition Alert/Sql Server Databases/