Process of Creating a Data Mart

"

Comment: The reason for creating and using the triggers is to avoid unnecessary increments of the sequences during merge-update operations which

  I want to avoid - Not exactly sure if the triggers would help in keeping a gap-less sequence.

"

You're never going to achieve gap-less-ness utilizing sequences. Which is totally fine, you need a guaranteed mechanism to generate unique numbers, that's what a sequence is for, nothing else.

With respect to using triggers, you'll want to avoid that like the plague in a data-mart / warehouse. Unless you're dealing with a very small set up, triggers will greatly inhibit your ability to scale the code. This also goes for foreign key constraints (again, really depends on your set up .. it's possibly you'd be fine with enabled foreign keys, it's possible you would set them to rely novalidate because you need faster loading capabilities). You'd also want to explore things like partition exchange, compression, etc...

That's about all I really have for you unfortunately. I'm by no means a warehouse guru, but I know enough to let you know that it's highly unlikely you're going to be able to flush out ALL the things you need to know to properly implement this given that you're new to Oracle (and data warehouses) on your own (even via the forum ... it's just too broad a topic to impart all the necessary knowledge).

You should really be looking for someone in house to help mentor you along with this. The documentation is a great place to start and I would highly encourage you to read as much of that as you can, but having someone in your organization with hands on experience can be an invaluable asset. Short of that you're going to learn on the job, and that's going to be a painful road to traverse.

Cheers,