MySQL trigger On Insert/Update events
Solution 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;
Solution 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 ;
Related videos on Youtube
Drewness
Full Stack web developer creating enterprise applications using C# .NET and Angular.
Updated on September 09, 2020Comments
-
Drewness over 3 years
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
whenext_words.word
is updated.
To further complicate matters,
- 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.
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..?-
Grijesh Chauhan about 11 yearsYou may like to view this, MySQL Fire Trigger for both Insert and Update Did my solution worked ? What was the result I would like to improve. Remember you can't update same table on which you calls trigger in MySQL
-
Drewness about 11 years@GrijeshChauhan - Thanks for the link! I am not able to get your answer working, but that is because I am new to conditional statements in MySQL, not because of your solution.
-
Drewness about 11 yearsI mean that when ext_words is updated, if the word is not in ext_words_count then I want to insert it and make the count 1. Otherwise if the word is in ext_words_count, increase the count by 1...
-
Grijesh Chauhan about 11 yearsGot it of-course for this my query will not work wait...
-
Ujjwal Prajapati over 10 yearsI have a quick question here. If you are storing the words in comma seperated form in some other table like "this, that", can we write a trigger on this table to split the value and store it in ext_words and ext_words_count?
- update
-
Grijesh Chauhan about 11 yearsnice So which form helped you DB.exchange?
-
Drewness about 11 yearsNever heard from anyone. :( I was just browsing through some of the articles. Thanks again for all of your help and pointing me to the other site!
-
Kiwi over 9 yearsThis trigger works fantastically in my MySQL server to filter out redundant inserts.