触发器+存储过程应用

DELIMITER $$
DROP PROCEDURE IF EXISTS proce_update_unrecoveryIncidentNum$$
CREATE PROCEDURE proce_update_unrecoveryIncidentNum(IN assetUuid CHAR(50))
BEGIN


  -- 删除事件，更新未恢复事件数
SET @count=0;
-- 更新未恢复事件数
SELECT
COUNT INTO @count  -- 赋值
FROM
incident_count c
LEFT JOIN threshold_rule_group g ON c.groupUuid = g.uuid
LEFT JOIN threshold_info i ON g.thresoldInfoUuid = i.uuid
WHERE 1=1
AND i.isDeleted = '0'
AND c.isDelete = '0'
AND c.assetUuid = assetUuid
AND c.isRecoveryIncident = '0'
AND c.type != '7';


UPDATE monitor_itcomp SET unrecoveryIncidentNum=@count WHERE uuid=assetUuid;


END $$




-- 触发器--保存事件，更新未恢复事件数
DELIMITER $$
DROP TRIGGER IF EXISTS t_incident_count_insert $$
CREATE TRIGGER t_incident_count_insert
AFTER INSERT ON incident_count
FOR EACH ROW
BEGIN
    -- 保存事件，更新未恢复事件数
CALL proce_update_unrecoveryIncidentNum(new.assetUuid);
END $$






-- 触发器-删除事件，更新未恢复事件数
DELIMITER $$
DROP TRIGGER IF EXISTS t_incident_count_delete $$
CREATE TRIGGER t_incident_count_delete
AFTER DELETE ON incident_count
FOR EACH ROW
BEGIN
    -- 删除事件，更新未恢复事件数
CALL proce_update_unrecoveryIncidentNum(old.assetUuid);
END $$