MySQL-触发案列

一：更新案例

DELIMITER $$
USE `haochacang`$$
DROP TRIGGER /*!50032 IF EXISTS */ `customer_info_update`$$
CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `customer_info_update` AFTER UPDATE ON `customer_info` 
    FOR EACH ROW 
    BEGIN
    IF new.category != old.category THEN
      UPDATE `sys_admin` SET sys_role_id = new.category WHERE sys_admin.cid = old.id ;
    END IF ;
    
    IF new.customer_name != old.customer_name THEN
      UPDATE `customer_store` SET store_name = new.customer_name WHERE customer_store.customer_id = old.id ;
    END IF ;
   END;
$$
DELIMITER ;

二：删除案例

DELIMITER $$
USE `haochacang`$$
DROP TRIGGER /*!50032 IF EXISTS */ `customer_info_delete`$$
CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `customer_info_delete` AFTER DELETE ON `customer_info` 
    FOR EACH ROW    
    BEGIN
    DELETE FROM `customer_login` WHERE customer_id = old.id;    
    DELETE FROM `customer_store` WHERE customer_id = old.id;    
    DELETE FROM `sys_admin` WHERE cid = old.id;
    DELETE FROM `order_info` WHERE customer_id = old.id;
    DELETE FROM `order_shop_cart` WHERE customer_id = old.id;
    DELETE FROM `order_shipping` WHERE customer_id = old.id;
    END;
$$
DELIMITER ;