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.