mysql - 使用包含计数函数的 where 条件实现 MySQL 触发器

Question

要求是实现一个触发器，用于在过去 1 小时内多次检查异常表中是否有满足一组条件的条目。基本上我们不想收到有关暂时性异常的警报。

这就是我所得到的；

CREATE TRIGGER trException AFTER INSERT ON Exception
 FOR EACH ROW
 BEGIN
  IF NEW.ExceptionMessage like '%The operation is timed out%' or 
    NEW.ExceptionMessage like '%https://....%' THEN
    INSERT INTO NotificationLog ('Message','InsertedTime') SELECT 'Time out error',now() 
    WHERE (SELECT COUNT(ExceptionId) FROM Exception 
           WHERE CreateTimestamp > NOW() - INTERVAL 1 HOUR 
           AND ExceptionMessage LIKE '%The operation has timed out%' 
           AND ExceptionMessage LIKE '%https://....%') > 10;
  ELSEIF NEW.ExceptionMessage like '%Error...%' THEN
            ...Next insert scenario with critria
  END IF;
END;

我认为问题在于嵌入式 SELECT 语句中的计数。该代码在插入语句之外运行时工作正常。我该怎么做才能解决这个问题？

Answer 1

我已经解决了这个问题。通过添加一个表 (ExceptionNotificationMessage) 来保存通知消息，我能够向第一个 SELECT 语句添加 FROM 子句，从而解决了问题。

CREATE TRIGGER trException AFTER INSERT ON Exception
 FOR EACH ROW
 BEGIN
  IF NEW.ExceptionMessage like '%The operation is timed out%' or 
    NEW.ExceptionMessage like '%https://....%' THEN
    INSERT INTO NotificationLog ('Message','InsertedTime') 
      SELECT 'Message','InsertedTime'
      **FROM ExceptionNotificationMessage** 
      WHERE (SELECT COUNT(ExceptionId) FROM Exception 
           WHERE CreateTimestamp > NOW() - INTERVAL 1 HOUR 
           AND ExceptionMessage LIKE '%The operation has timed out%' 
           AND ExceptionMessage LIKE '%https://....%') > 10;
  ELSEIF NEW.ExceptionMessage like '%Error...%' THEN
            ...Next insert scenario with criteria
  END IF;
END;

马丁