MySQL 在插入/更新事件上触发

MySQL trigger On Insert/Update events

提问by Drewness

So I have two tables like this...

所以我有两张这样的桌子......

ext_words
-------------
| id | word |
-------------
| 1  | this |
-------------
| 2  | that |
-------------
| 3  | this |
-------------

ext_words_count
---------------------
| id | word | count |
---------------------
| 1  | this |   2   |
---------------------
| 2  | that |   1   |
---------------------

I am trying to create a trigger that will:

我正在尝试创建一个触发器，它将：

• update ext_words_count.countwhen ext_words.wordis updated.

• 更新ext_words_count.count时ext_words.word更新。

To further complicate matters,

使事情进一步复杂化，

• if ext_words.worddoes not exist in ext_words_countwhen ext_wordsis updated, I would like to insert it into ext_words_countand set countas 1.

• 如果更新时ext_words.word不存在，我想将其插入并设置为1。ext_words_countext_wordsext_words_countcount

I have been looking at similar questions:
1. Before / after insert trigger using auto increment field, and
2. Using Trigger to update table in another database
trying to combine the 2. Here is what I have so far:

我一直在研究类似的问题：
1. 使用自动增量字段插入触发器之前/之后，以及
2. 使用触发器更新另一个数据库中的表，
试图结合 2。这是我到目前为止所拥有的：

DELIMITER $$
CREATE TRIGGER update_count
AFTER UPDATE ON ext_words
FOR EACH ROW
BEGIN

  UPDATE ext_words_count
    SET word_count = word_count + 1
  WHERE word = NEW.word;

END;
$$
DELIMITER ;

Any advice and direction is greatly appreciated. Or possibly another method that I have overlooked and as always thanks in advance!

非常感谢任何建议和方向。或者可能是我忽略的另一种方法，并且一如既往地提前致谢！

UPDATE:
I have opted for using 2 triggers, one for INSERT and one for UPDATE because I am not that familiar with conditional statements in MySQL.

更新：
我选择使用 2 个触发器，一个用于 INSERT，一个用于 UPDATE，因为我不太熟悉 MySQL 中的条件语句。

DELIMITER $$
CREATE TRIGGER insert_word AFTER INSERT ON ext_words
  FOR EACH ROW
    BEGIN
      INSERT IGNORE INTO ext_words_count (word) VALUES (NEW.word);
    END;
$$
DELIMITER ;

and

和

DELIMITER $$
CREATE TRIGGER update_word AFTER UPDATE ON ext_words
  FOR EACH ROW
    BEGIN
      UPDATE ext_words_count 
      SET word_count = word_count + 1 
      WHERE word = NEW.word;
    END;
$$
DELIMITER ;

The INSERT query is working great, however the UPDATE query is not updating word_count. Is there something I missed in the update query..?

INSERT 查询运行良好，但 UPDATE 查询未更新word_count。我在更新查询中遗漏了什么..？

回答by Drewness

With Grijesh's perfect help and his suggestion to use conditional statements, I was able to get ONEtrigger that does both tasks. Thanks again Grijesh

在 Grijesh 的完美帮助和他使用条件语句的建议下，我能够得到一个可以完成这两项任务的触发器。再次感谢格里耶什

 DELIMITER $$ 
 CREATE TRIGGER update_count AFTER INSERT ON ext_words 
 FOR EACH ROW 
   BEGIN
     IF NOT EXISTS (SELECT 1 FROM ext_words_count WHERE word = NEW.word) THEN
       INSERT INTO ext_words_count (word) VALUES (NEW.word);
   ELSE
       UPDATE ext_words_count SET word_count = word_count + 1 WHERE word = NEW.word;
   END IF;
  END $$    
 DELIMITER;   

回答by Aman Maurya

avoid use of keywords like count as its is used by the sql method some time its create error but some runs good

避免使用像 count 这样的关键字，因为它有时被 sql 方法使用，它的创建错误但有些运行良好

DELIMITER $$
 CREATE TRIGGER update_count
   AFTER UPDATE ON ext_words
     FOR EACH ROW
       BEGIN

          SELECT count INTO @x FROM ext_words_count LIMIT 1;
          UPDATE ext_words_count
          SET count = @x + 1
          WHERE word = NEW.word;

END;
$$
DELIMITER ;