Mysql 触发器相关

主要是触发Redis数据库更新,一般都有三种操作,对于数据表 AdminCustomerInfo

T_AdminCustomerInfo_AI -> AfterInsert

BEGIN
  DECLARE asId varchar(20);
  SET asId = CONCAT("C-",NEW.aiId);
  SET @ret= redis_command_v2("HSET","AccountInfoList",asId,
               json_object
               (
                   asId,
                   NEW.aiId AS aiId,
                   NEW.identityCardId AS identityCardId,
                   NEW.aInfoName AS aInfoName,
                   NEW.aInfoPicture AS aInfoPicture,
                   NEW.aInfoPhone AS aInfoPhone,
                   NEW.aInfoEmail AS aInfoEmail,
                   NEW.companyJoinState AS companyJoinState,
                   NEW.companyId AS companyId,
                   NEW.aInfoQQ AS aInfoQQ,
                   NEW.qqBind AS qqBind,
                   NEW.weChatBind AS weChatBind,
                   NEW.weiBoBind AS weiBoBind,
                   NEW.mailNotify AS mailNotify,
                   NEW.smsNotify AS smsNotify,
                   NEW.weChatNotify AS weChatNotify,
                   NEW.accountName AS accountName,
                   NEW.Password AS Password,
                   NEW.aInfoLevel AS aInfoLevel,
                   NEW.Balance AS Balance,
                   NEW.lastLogin AS lastLogin,
                   NEW.manId AS manId
               )
  );
  INSERT INTO App_RunInfo( asId )VALUES( asId );
END

T_AdminCustomerInfo_AU -> AfterUpdate

BEGIN
  DECLARE asId varchar(20);
  SET asId = CONCAT("C-",NEW.aiId);
  SET @ret= redis_command_v2("HSET","AccountInfoList",asId,
               json_object
               (
                   asId,
                   NEW.aiId AS aiId,
                   NEW.identityCardId AS identityCardId,
                   NEW.aInfoName AS aInfoName,
                   NEW.aInfoPicture AS aInfoPicture,
                   NEW.aInfoPhone AS aInfoPhone,
                   NEW.aInfoEmail AS aInfoEmail,
                   NEW.companyJoinState AS companyJoinState,
                   NEW.companyId AS companyId,
                   NEW.aInfoQQ AS aInfoQQ,
                   NEW.qqBind AS qqBind,
                   NEW.weChatBind AS weChatBind,
                   NEW.weiBoBind AS weiBoBind,
                   NEW.mailNotify AS mailNotify,
                   NEW.smsNotify AS smsNotify,
                   NEW.weChatNotify AS weChatNotify,
                   NEW.accountName AS accountName,
                   NEW.Password AS Password,
                   NEW.aInfoLevel AS aInfoLevel,
                   NEW.Balance AS Balance,
                   NEW.lastLogin AS lastLogin,
                   NEW.manId AS manId
               )
  );
END

T_AdminCustomerInfo_AD -> AfterDelete

BEGIN
  DECLARE asId varchar(20);
  SET asId = CONCAT("C-",OLD.aiId);
  SET @ret= redis_command_v2("HDEL","AccountInfoList",asId);
  DELETE FROM App_RunInfo WHERE asId = asId;
END

其它暂存

AccountTransaction => AccountTradePays 表同步

一. 同步插入交易记录

DELIMITER ||
    DROP TRIGGER IF EXISTS T_AfterInsert_ON_AccountTransaction ||
    CREATE TRIGGER T_AfterInsert_ON_AccountTransaction
    AFTER INSERT ON AccountTransaction
    FOR EACH ROW
    BEGIN
        INSERT INTO AccountTradePays( pSerial, asId, tradeMoney, tradeWay, tradeSource, tradeTitle, tradeContent, tradeState, cBalance )
        VALUES( NEW.tSerial, NEW.asId, NEW.tradeMoney, NEW.tradeWay, NEW.tradeSource, NEW.tradeTitle, NEW.tradeContent, NEW.tradeState, NEW.cBalance );
    END||
DELIMITER

DELIMITER &&
CREATE TRIGGER tri_memory_insert AFTER Insert ON T FOR EACH ROW
BEGIN
    insert into tempT(id,strName) VALUES(NEW.id,NEW.strName);
END &&
DELIMITER ;

二. 同步更新触发器

DELIMITER $$
    /*[DEFINER = { user | CURRENT_USER }]*/
    CREATE TRIGGER `a`.`触发器名` BEFORE UPDATE ON `a`.`table1`
    FOR EACH ROW BEGIN
        IF new.id != old.id THEN
            UPDATE `b`.`table2` SET `b`.`table2`.id=new.id WHERE `b`.`table2`.val=old.val;
        END IF;
    END$$
DELIMITER;

DELIMITER &&
    CREATE TRIGGER T_AfterUpdate_ON_AccountTransaction
    AFTER UPDATE ON AccountTransaction
    FOR EACH ROW
    BEGIN
        UPDATE AccountTradePays SET tradeState=NEW.tradeState WHERE pSerial = "AT-" + OLD.tId;
    END &&
DELIMITER ;

三. 同步删除触发器

DELIMITER ||
    DROP TRIGGER IF EXISTS T_AfterDelete_ON_AccountTransaction ||
    CREATE TRIGGER T_AfterDelete_ON_AccountTransaction
    AFTER DELETE ON AccountTransaction
    FOR EACH ROW
    BEGIN
        DELETE FROM AccountTradePays WHERE pSerial = "AT-" + OLD.tId;
    END||
DELIMITER;

DELIMITER &&
    CREATE TRIGGER tri_memory_delete AFTER DELETE ON T FOR EACH ROW
    BEGIN
        DELETE From tempT where tempT.id = OLD.id;
    END &&
DELIMITER ;

DECLARE vv varchar(20);
    set vv=InsertToEmail(names,"zyff@iirii.com");
    INSERT INTO user_info (name,age,email,addr) VALUES(names,33,"zyf@iirii.com","湖北东西湖");


BEGIN
    DECLARE vv varchar(20);
    set vv=InsertToEmail(names,"zyff@iirii.com");
    INSERT INTO user_info (name,age,email,addr) VALUES(names,33,"zyf@iirii.com","湖北东西湖");
END

BEGIN
    DECLARE vv varchar(20);
    DECLARE vvv varchar(20);
    set vv=InsertToUploadsFun(names,"zyfffff@iirii.com");
    set vvv=InsertToEmailFun(names);
    INSERT INTO user_info (name,age,email,addr) VALUES(names,33,"zyf@iirii.com","湖北东西湖");
END

BEGIN
  DECLARE asId varchar(20);
  SET asId = CONCAT("C-",NEW.Id);
  SET @ret= redis_command_v2("HSET","crmInboxEventsS",asId,
                            json_object
                            (
                                  json_members
                                  (
                                          "op",
                                          "insert",
                                          "asId",
                                          asId,
                                          "value",
                                          json_object
                                          (
                                              NEW.Id as "id",NEW.type as "type",
                                              NEW.mailserver_id as "mailserverId",NEW.sender as "sender",
                                              NEW.sender_name as "senderName",NEW.recevier as "recevier",
                                              NEW.replyto as "replyto",NEW.bbemails as "bbemails",
                                              NEW.ccemails as "ccemails",NEW.subject as "subject"
                                          )
                                      )
                              )
                        );
END

BEGIN
  DECLARE asId varchar(20);
  SET asId = CONCAT("C-",NEW.Id);
  SET @ret= redis_command_v2("HSET","crmInboxEventsS",asId,
               json_object
               (
                   NEW.Id as "id",NEW.type as "type",
                   NEW.mailserver_id as "mailserverId",NEW.sender as "sender",
                   NEW.sender_name as "senderName",NEW.recevier as "recevier",
                   NEW.replyto as "replyto",NEW.bbemails as "bbemails",
                   NEW.ccemails as "ccemails",NEW.subject as "subject"
               )
  );
END

参考列表
MySQL触发器