MySQL trigger On Insert/Update events

Question

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..?

Answer 1

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;    

Answer 2

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

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 ;