Insert rows in a table, via a row-level trigger, activated after insertion in that same table

Question

I have to design a schema and trigger system in SQL that will save words and their synonyms.

I thought of this basic schema:

create table words (
 id int primary key auto_increment,
 word varchar(50)
);
create table synonyms(
 w1 int references words(id),
 w2 int references words(id),
 primary key (w1, w2)
);

Another requirement is to make the synonyms relation symmetric (if a is a synonym of b, then b is a synonym of a and the table synonyms should be updated accordingly).

I thought of using a trigger that looks like this:

create trigger MakeSynonymsSymmetric
after insert on synonyms
for each row
insert into synonyms
values (new.w2, new.w1); 

Which of course won't work because the trigger activates itself generating an infinite loop (or at least that's my understanding).

Trying to insert into synonyms gives me this error:

Can't update table 'synonyms' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any way around this problem by defining the schema in a different way, but always using triggers?

Answer 1

To perform what you are looking for based on this example:

INSERT INTO synonym_bucket VALUES (23,37);

SUGGESTION #1

DROP TABLE IF EXISTS synonym_bucket;
CREATE TABLE synonym_bucket LIKE synonyms;
ALTER TABLE synonym_bucket ENGINE=BLACKHOLE;
DELIMITER $$ 
CREATE TRIGGER MakeTwoSynonyms
AFTER INSERT ON synonym_bucket 
FOR EACH ROW
BEGIN
 IF new.w1 <> new.w2 THEN
 INSERT IGNORE INTO synonyms VALUES (new.w1, new.w2),(new.w2, new.w1);
 END IF;
END $$
DELIMITER ;

Using synonym_bucket takes up just 2K (table definition plus trigger)

Bottom Line: For a table's before and after trigger, you cannot perform additional DML to the same table. However, you can do DML from a trigger to another table. This is why I made a BLACKHOLE table. It takes up to 2K (table definition and the trigger code). You use it to write to a separate table. In this case, synonym_bucket does the double INSERT into synonyms.

SUGGESTION #2

Do the the double INSERT yourself

INSERT IGNORE INTO synonyms VALUES (23,37),(37,23); 

No trigger required

GIVE IT A TRY !!!

• Francesco: First of all, thank you. The solution seems to work, however I hoped in a somehow "cleaner" solution and don't really understand the necessity of setting ENGINE=BLACKHOLE
• RolandoMySQLDBA: I updated the synonym_backet table's trigger and added an explanation.