MySQL在插入/更新事件上触发 [英] MySQL trigger On Insert/Update events
问题描述
所以我有两个这样的表...
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:
- 在更新
ext_words.word
时更新ext_words_count.count
.
- update
ext_words_count.count
whenext_words.word
is updated.
进一步复杂化,
- 如果更新
ext_words
时ext_words_count
中不存在ext_words.word
,我想将其插入ext_words_count
并将count
设置为1.
- if
ext_words.word
does not exist inext_words_count
whenext_words
is updated, I would like to insert it intoext_words_count
and setcount
as 1.
我一直在寻找类似的问题:
1. 使用自动增量字段在插入触发之前/之后和
2. 使用触发器更新另一个数据库中的表
试图结合2.这是我到目前为止所拥有的:
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:
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!
更新:
我选择使用2个触发器,一个用于INSERT,一个用于UPDATE,因为我对MySQL中的条件语句不太熟悉.
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.
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 ;
和
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 ;
INSERT查询工作得很好,但是UPDATE查询没有更新word_count
.
The INSERT query is working great, however the UPDATE query is not updating word_count
. Is there something I missed in the update query..?
推荐答案
有了Grijesh的完美帮助和他对使用条件语句的建议,我得以得到 ONE 触发器,它可以完成这两项任务.再次感谢Grijesh
With Grijesh's perfect help and his suggestion to use conditional statements, I was able to get ONE trigger that does both tasks. Thanks again 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;
这篇关于MySQL在插入/更新事件上触发的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!