0

I have 2 tables 1st table word_cloud (auto inc), predefined_text, content_id . 2nd table word_cloud_count (auto inc),predefined_text,content_id,count . I am calling a trigger when data is inserted into word_cloud, which basically groups the word, calculates the count and inserts it into word_cloud_count

word_cloud table consists of 

id | predefined_text | content_id
---------------------------------
 1 | ABC             | 1
 2 | ABC             | 1
 3 | ABC             | 1
 4 | XYZ             | 2
 5 | XYZ             | 2
 6 | XYZ             | 2
 7 | XYZ             | 1
 8 | ABC             | 2
 9 | ABC             | 2
10 | ABC             | 2

word_cloud_count consists of 

id | predefined_text | content_id | count
-----------------------------------------------
 1 | ABC             | 1          |  3
 2 | XYZ             | 2          |  3
 3 | ABC             | 2          |  3
 4 | XYZ             | 1          |  1

Trigger Function

CREATE TRIGGER word_cloud_count_insert after insert on word_cloud
    INSERT INTO word_cloud_count (Word,content_id ,count )
    SELECT predefined_text,content_id, count(*)
    FROM word_cloud
    group by predefined_text,content_id

Everything works fine and the values are being inserted in the word_cloud_count table

However my problem is every time the data is inserted into word_cloud the trigger is called and duplicate values are being stored into word_cloud_count

How do i avoid this

Any suggestions would be of great Help, Thanks in advance

D Shashank
  • 1
  • 4
  • Your trigger makes no sense. Where is the "before" or "after", for instance? Also show the structure of your tables, and what you want the trigger to do. – Gordon Linoff Aug 24 '17 at 00:54
  • @GordonLinoff Every time a value is inserted into word_cloud my trigger is called and it inserts the same values into the word _cloud_count table , i want my trigger to update the counter if the same values are present if not it should insert . – D Shashank Aug 24 '17 at 01:30

1 Answers1

1

You can use upsert query like Insert into .... om duplicate. Check here Insert into a MySQL table or update if exists

kawadhiya21
  • 2,263
  • 17
  • 32