Ask the Expert

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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top