I've got a table that describes some relation between two items. The items are referenced by
ID. I'd like to create a trigger, so whenever you create a row
(a,b), a symmetrical row
(b,a) is created. I've tried creating after trigger, but it exceed stack depth, because it called itself on insert it had within itself. What is the proper way to do it?
Table would look somewhat like this:
CREATE TABLE MY_RELATION( ID SERIAL, THING1 INT NOT NULL, THING2 INT NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE MY_THING( ID SERIAL, VAL INT NOT NULL PRIMARY KEY(ID) ); ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING1) REFERENCES MY_THING(ID); ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING2) REFERENCES MY_THING(ID);
(thing1, thing2)as well (or even to replace the existing PK)?