0

So here is what my table looks like:

enter image description here

What I want to do is after a insert on the table, generate all the prerequisites for a certain class. So if I do this input:

INSERT INTO Prerequisite(classID, term, year, prereqID)
       VALUES(220, 'Fall', 2016, 200);

It would go ahead and insert that, but since class 200 is prerequisite it would go a look if class 200 has any prerequisites itself. And if it finds one, which it does it would go ahead an insert this into the database:

INSERT INTO Prerequisite(classID, term, year, prereqID)
       VALUES(220, 'Fall', 2016, 197);

Then it would go look to see if class 197 has a prerequisites and so on. I do not have a lot of experience with triggers any help would be appreciated.

1 Answer 1

2

I think your case is a transaction; first figure out the solution to the problem in general, then consider whether a trigger is needed.

The table structure you have is a common pattern, in which the table has a foreign key to itself -- in this case prereqid is a reference to one or more other records in the same table. You are tracking courses, it seems, and one attribute of a course is that it may require that you take one or more other courses first.

Consider this important question: are prerequisites strictly hierarchical? For example, do you have to take CS 100 before taking CS 101, and CS 101 before taking CS 200? In this case, the prerequisites of CS 200 are 100, and 101 ... but (importantly) each course has only one prerequisite -- they form a chain. In this case, you don't need a trigger, because presumably the prerequisite of a course is known when you insert it's record.

In contrast, consider an econ course in econometrics, Econ 200, which requires both that you have taken Econ 100 and Math 102. This is the more likely scenario, and in this case your data model does not handle this case well, so that's the first thing to deal with.

In the second case, you have a relationship: one course has from zero to many prerequisites, and by thinking of it this way, you would model this as two tables: the course table that has information about term and year, and another that has has a foreign key to the courses table. Again, inserting into the courses table requires that you know 1) the information about the course, and 2) the courses that are its prerequisites.

If you captured this in one screen, to insert a new course, you would need to start a transaction. The first command would insert into the courses record, and (presumably) auto-generate the primary key of the course. Then, you would need to use the primary keys of each of the prerequisite courses and, for each insert a new row into the prerequisites table. When all of this was done, you would commit the transaction and all records would be written. In this case, you could do this as a stored procedure (in this case perhaps a trigger), or just as a transaction in program code.

I suspect you'll look at this and say, "yeah, but I just need to figure out how to write a trigger in PostgreSQL". The easy part is the CREATE TRIGGER command; the hard part is the CREATE FUNCTION -- that's where you'll need to decide how to implement it, and where you would decide that this must be an atomic transaction, and some other stuff. Ask a more specific question when you get there.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.