MySQL 触发器的定义

 

-- Insert
DELIMITER $$

USE `testdatabase`$$

DROP TRIGGER /*!50032 IF EXISTS */ `Trigger_XXX_INSERT`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `Trigger_XXX_INSERT` AFTER INSERT ON `TB_XXX` 
    FOR EACH ROW BEGIN
    DECLARE newStatus INT;
    SET newStatus = NEW.`Status`;
        -- 省略do something
    END;
$$

DELIMITER ;

-- Update
DELIMITER $$

USE `testdatabase`$$

DROP TRIGGER /*!50032 IF EXISTS */ `Trigger_XXX_UPDATE`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `Trigger_XXX_UPDATE` BEFORE UPDATE ON `TB_XXX` 
    FOR EACH ROW BEGIN
    DECLARE oldStatus INT;
    DECLARE newStatus INT;
    SET oldStatus = OLD.`ConsultationStatus`;
    SET newStatus = NEW.`ConsultationStatus`;
    
    IF (newStatus <> oldStatus) 
    THEN 
        -- continue do something
    END IF;
    END;
$$

DELIMITER ;