mysql触发器

更新
DROP TRIGGER IF EXISTS `t_afterupdate_on_activities`;
CREATE TRIGGER t_afterupdate_on_activities 
AFTER UPDATE ON activities
FOR EACH ROW
BEGIN
SET @newkeywords = new.keyword;
  SET @oldkeywords = old.keyword;

IF old.is_deleted = 0 AND old.keyword IS NOT NULL THEN
WHILE @oldkeywords is not null and trim(@oldkeywords) != '' DO
  set @oldkeyword = trim(SUBSTRING_INDEX(@oldkeywords,',',1));
set @oldkeyword2 = trim(SUBSTRING_INDEX(@oldkeywords,',',2));
 set @oldcount = (select count(*) from keyword WHERE keyword = @oldkeyword);
 IF  @oldcount > 0 THEN
UPDATE keyword SET count = (CASE WHEN count IS NULL THEN 0 WHEN count - 1 < 0 THEN 0 ELSE count - 1 END) WHERE keyword = @oldkeyword;
END IF;
SET @oldkeywords = (CASE WHEN @oldkeyword = @oldkeyword2 THEN '' ELSE trim(SUBSTR(@oldkeywords FROM CHAR_LENGTH(@oldkeyword) + 2)) END);
  END WHILE;
END IF;


  IF new.is_deleted = 0 AND new.keyword IS NOT NULL THEN
WHILE @newkeywords is not null and trim(@newkeywords) != '' DO
set @newkeyword = trim(SUBSTRING_INDEX(@newkeywords,',',1));
set @newkeyword2 = trim(SUBSTRING_INDEX(@newkeywords,',',2));
set @newcount = (select count(*) from keyword WHERE keyword = @newkeyword);
IF @newcount = 0 THEN
INSERT INTO keyword(keyword,count,is_deleted) VALUES(@newkeyword,1,0);
ELSE
UPDATE keyword SET count = (CASE WHEN count IS NULL THEN 1 ELSE count + 1  END) WHERE keyword = @newkeyword;
END IF;
SET @newkeywords = (CASE WHEN @newkeyword = @newkeyword2 THEN '' ELSE trim(SUBSTR(@newkeywords FROM CHAR_LENGTH(@newkeyword) + 2)) END);
END WHILE;
END IF;
END;


插入


DROP TRIGGER IF EXISTS `t_afterinsert_on_activities`;
CREATE TRIGGER t_afterinsert_on_activities 
AFTER INSERT ON activities
FOR EACH ROW
BEGIN
SET @newkeywords = new.keyword;
  IF new.is_deleted = 0 THEN
WHILE @newkeywords is not null and trim(@newkeywords) != '' DO
set @newkeyword = trim(SUBSTRING_INDEX(@newkeywords,',',1));
set @newkeyword2 = trim(SUBSTRING_INDEX(@newkeywords,',',2));
set @newcount = (select count(*) from keyword WHERE keyword = @newkeyword);
IF @newcount = 0 THEN
INSERT INTO keyword(keyword,count,is_deleted) VALUES(@newkeyword,1,0);
ELSE
UPDATE keyword SET count = (CASE WHEN COUNT IS NULL THEN 1 ELSE count + 1 END) WHERE keyword = @newkeyword;
END IF;
SET @newkeywords = (CASE WHEN @newkeyword = @newkeyword2 THEN '' ELSE trim(SUBSTR(@newkeywords FROM CHAR_LENGTH(@newkeyword) + 2)) END);
END WHILE;
END IF;
END;




删除


DROP TRIGGER IF EXISTS `t_afterdelete_on_activities`;
CREATE TRIGGER t_afterdelete_on_activities 
AFTER DELETE ON activities
FOR EACH ROW
BEGIN
SET @oldkeywords = old.keyword;
  IF old.is_deleted = 0 THEN
WHILE @oldkeywords is not null and trim(@oldkeywords) != '' DO
set @oldkeyword = trim(SUBSTRING_INDEX(@oldkeywords,',',1));
set @oldkeyword2 = trim(SUBSTRING_INDEX(@oldkeywords,',',2));
set @oldcount = (select count(*) from keyword WHERE keyword = @oldkeyword);
IF @oldcount > 0 THEN
UPDATE keyword SET count = (CASE WHEN COUNT IS NULL THEN 0 WHEN count - 1 < 0 THEN 0 ELSE count - 1 END) WHERE keyword = @oldkeyword;
END IF;
SET @oldkeywords = (CASE WHEN @oldkeyword = @oldkeyword2 THEN '' ELSE trim(SUBSTR(@oldkeywords FROM CHAR_LENGTH(@oldkeyword) + 2)) END);
END WHILE;
END IF;
END;