Drewness Drewness - 2 months ago 18
MySQL Question

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:


  • 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

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;   
Comments