MySQL trigger On Insert/Update events
insert, insert-update, mysql, triggers
Solution
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;
Problem
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.count` when `ext_words.word` is updated. To further complicate matters, - if `ext_words.word` does not exist in `ext_words_count` when `ext_words` is updated, I would like to insert it into `ext_words_count` and set `count` as 1. 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! 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 ; ``` 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..?