I have a web app that logs invalid login attempts to a database table.
Is there a way to create a trigger on the table that only runs after a certain number of records have been created in the last hours or so, short of something like if count(*) > 100?
The end goal is to send an email if there are an abnormal number of failed login attempts.
You can't. The best option is to put the check inside the trigger itself.
Create a trigger that runs for every case, and have it see if the criteria for further action are met. If continuing actions aren't desired (e.g. for attempt 102 in the past hour) you could record the time it last took such action in another table, then make the criteria that it must be > 100 failed attempts per hour AND it must be an hour since the last such action.
Don't do this with a trigger. Create a job to monitor the count of failed logins in the last hour and send an email if it is over a certain threshold. Have it run hourly.
Why a trigger? create a SQL agent jobs that runs every n minutes. You check if in the last hour there where more than 100 invalid login attempts, if this is true run a database mail proc with the details, look up sp_send_dbmail