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