Thinking About Triggers

I got my Oracle 12c database up and running. Time to start developing. Put together a small script to create a few tables. Also created sequences to populate the IDs in the tables. I thought I would get automated and have insert triggers on the tables setting the ID values. Sweet I thought.

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?