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?
Best Answer
To perform what you are looking for based on this example:
SUGGESTION #1
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 intosynonyms
.SUGGESTION #2
Do the the double INSERT yourself
No trigger required
GIVE IT A TRY !!!