SQLTeam.com | Weblogs | Forums

SQL Trigger if Data in table meets Criteria


#1

Hello looking for a trigger to alert me if my queue table has data in it like this:

Select CONVERT(NVARCHAR, datestarted, 120) as BatchDate,
getdate() as RptDate,
CONVERT(varchar(5),DATEADD(minute, DATEDIFF(minute, datestarted, getdate()), 0), 114) as qTimeElapse,
"SendAlert" = CASE WHEN DATEDIFF(minute,datestarted,getdate()) >= 30 THEN 'YES' ELSE 'NO' END
from [queue]

I would like to have SQL mail alert me. Unless their is a better way than a trigger. Thanks


#2

I wouldn't put anything that has potential to "stall" (such as Email) in a Trigger. I would have a task running frequently that checked the Queue for entries or, if the presence / absence of a row is not suitable for the Alert, then I would have a Trigger (on the Queue table) set a FLAG or insert a ROW into some sort of monitoring table, which my frequently running task could check.