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触发器