replicate table in the same server [message #341672] |
Tue, 19 August 2008 22:24 |
TJPokala
Messages: 17 Registered: March 2008
|
Junior Member |
|
|
Hi all,
I have a table A at schema A which gets updated every week.
I would liek to replicate this table within thesame server so this table A is not touched and that manipulation will be done in the replicated table.
Will trigger help me do this?
create or replace trigger a_replica
before insert or update on a
for each row
begin
DELETE FROM b;
INSERT INTO b
SELECT * FROM A;
END;
-I do not want a snapshot because the main reason is to dml on the replicated table.
-this trigger should be able to copy tables synchronously in real time
This is what I think the framework should be:
CREATE OR REPLACE TRIGGER A_replica
AFTER INSERT ON A FOR EACH ROW BEGIN
IF ????(--create a sequence here?,how to identify new columns)THEN
INSERT INTO B
SELECT * FROM A ;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, sqlcode() || ': ' ||sqlerrm());
END;
/
Any advise?
|
|
|
|
Re: replicate table in the same server [message #341674 is a reply to message #341672] |
Tue, 19 August 2008 22:45 |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
Hi,
Need more information about the case,
1) whether this main table and another table would be in the same schema or different schema?
2) You can use trigger, but as you demonstrate, why you are deleting all rows from table and then insterting all the content from main table?
You are using row level triiger, so you can insert just the newly inserted row using :new.columnname clause.
3) If that table will going to be used just as a lookup table, then instade of creating it and filling it each time, you can create a synonym of main table and use it.
4) Other opetions for doing it is streams or materialized view, aur just simple view ( you need to pick correct one according the requirement).
Regards,
Dipali..
[Updated on: Tue, 19 August 2008 22:46] Report message to a moderator
|
|
|
|