Went through a couple iterations until I got the basic table definitions the way I wanted. Then I coded up a procedure to insert records into the tables. Something strange was happening though. The IDs were not getting set. I thought I manually tested the triggers from the SQL*Plus command prompt.
Turns out my trigger became invalid when I kept dropped and recreating the tables. Easy enough to fix. Just recompile the triggers. Then I did some philosophical thinking about my setup. I insert a new row of data. The trigger populates the ID. How do I then find out what that ID is if I want to insert it as a foreign key in some table?
In the old days, I would let my procedures explicitly generate the IDs. That way I had the ID before the INSERT, and I could then use the value I stored in a variable to use as foreign keys. Now that the IDs are set automatically in triggers, I got to query the database after the fact. There has to be a better way to do this. Any ideas?
