I have a table, call it EVENTS, where each row can depend on 0 or more other rows in the table. I need a way of representing this relationship that also prevents circular dependencies (i.e. a group of events leading back into an event in that same group).
I currently have a link table external to EVENTS, call it EVENTS_DEP. This table links dependent rows to the rows they depend on and allows for multiple dependencies on one row. How would I prevent circular dependencies using such a table?
NOTE: if it is at all possible to do this only with the database design (not with scripts, triggers, etc.), this would be ideal.
Also, if this can only be done using triggers, please let me know what kind of trigger (i.e. on what event) it should be run on (on insert, maybe?).