Everytime there is an Insert, Update or Delete on TABLE 1, I have to delete all the existing records in TABLE 2 and repopulate it back with the records from TABLE 1. What is the best method in doing so? Would this be a statement trigger that involves a type of collection? If so, what type of collection (e.g. varrays, nested)? I'm a novice at this, and I'm not sure what to read. Thank you.
OK...here's my question for you: WHY? What I think you're saying is that table 2 is a duplicate of table 1. First of all, what's the purpose of having two identical tables and if you've got a good reason/purpose for it, why do you want to delete everything and repopulate the entire table 2 for a single row change in table 1?
If you really do need this duplicate table functionality, then you can certainly use a trigger to create the new/updated record in table 2 when I/U/D's are done on table 1. Here's some pseudo-code for how you'd put the trigger together:
CREATE TRIGGER . . . BEFORE/AFTER INSERT, UPDATE, DELETE ON table_1 FOR EACH ROW BEGIN IF INSERTING INSERT INTO table_2 VALUES (:new.col1, :new.col2, :new.col3..)...and so on IF UPDATING UPDATE table_2 SET col1 = :new.col1, col2 = :new.col2.....and so on WHERE key_column = :old.key_column ; *** another alternative for an UPDATE is that you could DELETE the row that's *** there and then insert a new one as follows DELETE FROM table_2 WHERE key_column = :old.key_column ; INSERT INTO table_2 VALUES (same as when INSERTING) IF DELETING DELETE FROM table_2 WHERE key_column = :old.key_column ; END;By doing it this way, you only deal with one row at a time (in effect). This reduces the possibility of locking conflicts and also relieves the burden of activity caused by deleting and entire table just to turn around and replace it again with only minor changes.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 6
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 5
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 4
FAQ: Creating and altering SQL Server tables