Q

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;
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


This was first published in January 2003

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close