lundi 5 janvier 2015

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


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?





Aucun commentaire:

Enregistrer un commentaire