Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

1st SchemaTable's "On Data Change" --> Update another Schema.another Table

KnightbeatFeb 22 2010 — edited Feb 22 2010
I need to know how to write a trigger so that it listens to a table*(table 1)* of one schema & immediately updates another table(table 2) which exists in another schema, on any data change (record insert/update or delete) in the 1st table I mentioned.

[I didn't read anything about this before I ask this. So I just need to know whether it's possible or not. I don't expect you to spend a lot of time on this. But I will be much thankful, if you could tell me whether this is possible to be done or not]

Thanks!

Comments

Gary Graham-Oracle

Within the mission scope of the SQL Developer tool, the "drops, ddl + sqlldr table from scratch" approach seems both expected and sufficient.  Expanding the scope to things like replication or data integration, then you would expect to use a different tool.

If your use case for the replicated table is only some kind of testing, look into SQL Developer's Unit Test feature with its Startup and TearDown processing.

Regards,
Gary

SQL Developer Team

1 person found this helpful
Paavo

Thank you very much for your fast response and couraging guidance to try Unit Test which I never tried before. SqlDeveloper and DataModeller are great service for developers!

Unit Test will certainly make testing much easier for me in the future

Of course it was a bit challenging to setup XE where I can act as sysdba and have privilegies to build Unit Test repository etc..

What I have now discovered is that the startup and tear down scripts can be stored to library which can then be copied and optionally subscribed in all unit tests.

Alone this is a very good feature and has pretty polished touch, but one idea came to my mind that could this library be linked with the cart generated export scripts?

That way the Unit Test tear down could contain also ddl-scripts which are not allowed in user plsql-scripts and those could be managed with the cart features of the tool.

The ddl drops and creates for tables, sequences, triggers are necessary because the tear down scripts omit restoring sequences as they were before the unit test took place e.g. for procedures inserting to the tables.

btw. the CART copy-functionality could have similar setup dialog like the export-functionality has, because the copy-functionality attempts to define 'storage' tablespaces and such by default which makes it fail when fiddling between databases.

rgrds Paavo


1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 22 2010
Added on Feb 22 2010
#on, #sql, #trigger, #update
2 comments
414 views