1

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?

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 !!!

2
  • 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 – Francesco Jan 5 '15 at 20:33
  • I updated the synonym_backet table's trigger and added an explanation. – RolandoMySQLDBA Jan 5 '15 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.