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?