-- ----------------------------------------------------------------------------- -- function updateViewControllerLogLastActivityId qw_ViewControllerLogSession -- ----------------------------------------------------------------------------- DROP PROCEDURE IF EXISTS updateViewControllerLogLastActivityId ; DELIMITER $$$ CREATE PROCEDURE updateViewControllerLogLastActivityId(_sessionId char(64)) not deterministic modifies sql data BEGIN SET @lastActivityId := 0; SELECT MAX(`id`) INTO @lastActivityId FROM `qw_ViewControllerLog` WHERE `sessionId` = _sessionId GROUP BY `sessionId`; IF (@lastActivityId > 0) THEN UPDATE `qw_ViewControllerLogSession` SET `lastActivityId` = @lastActivityId WHERE `id` = _sessionId; ELSE UPDATE `qw_ViewControllerLogSession` SET `lastActivityId` = NULL WHERE `id` = _sessionId; END IF; END $$$ DELIMITER ; -- ----------------------------------------------------------------------------- -- triggers DELIMITER $$$ -- ----------------------------------------------------------------------------- -- ----------------------------------------------------------------------------- -- qw_ViewControllerLog -- ----------------------------------------------------------------------------- DROP TRIGGER IF EXISTS `ViewControllerLogSession_ai` $$$ CREATE TRIGGER `ViewControllerLogSession_ai` AFTER INSERT ON `qw_ViewControllerLog` FOR EACH ROW begin CALL updateViewControllerLogLastActivityId(NEW.sessionId); end $$$ DROP TRIGGER IF EXISTS `ViewControllerLogSession_ad` $$$ CREATE TRIGGER `ViewControllerLogSession_ad` AFTER DELETE ON `qw_ViewControllerLog` FOR EACH ROW begin CALL updateViewControllerLogLastActivityId(OLD.sessionId); end $$$ -- ----------------------------------------------------------------------------- DELIMITER ; -- end triggers -- -----------------------------------------------------------------------------