Trigger to update TABLE 2 with TABLE 1 updates

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;

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.