Home » SQL & PL/SQL » SQL & PL/SQL » automation script (oracle 10g)
automation script [message #312370] Tue, 08 April 2008 12:58 Go to next message
dr46014
Messages: 49
Registered: February 2007
Member
Hi All
I have a requirement to automate the process of inserting one record into the table each time a record is inserted into a table in a specific database.
Background:We have a table in our database called client_dim where a row wich contains client info is insrted into the table when a new client is added.Bassically this is done when a client is added in another database means client_dim table of that database.Both the tables are of different structure but the information is almost same.i need to automate the process like if a row is instrted in the other database's client_dim table a trigger will be invoked and one row will be populated which will select some values from other database table which got inserted and will populate our table with relevant values.
is this possible ? if yes then can anyone help me writing a sample trigger in the same direction i have mentioned the requirement earlier.

it wud be nice if you can provide me the script with some dummy schema and database names.
Not : one database is on oracle 9i and one is on oracle 10g.both the servers are also different

[Updated on: Tue, 08 April 2008 13:12] by Moderator

Report message to a moderator

Re: automation script [message #312392 is a reply to message #312370] Tue, 08 April 2008 14:31 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't have two databases, but I hope that this example will be of some help.

There's a 'test' table created in Scott's and Mike's schema, and it is empty. We'll create a database link from Scott to Mike which will be used a little bit later:
SQL> CONNECT mike/lion@ora10lp
Connected.
SQL> SELECT * FROM test;

no rows selected

SQL> CONNECT scott/tiger@ora10lp
Connected.
SQL> CREATE DATABASE LINK dbl_mike
  2  CONNECT TO mike
  3  IDENTIFIED BY lion
  4  USING 'ora10lp';

Database link created.

SQL> SELECT 'x' FROM dual@dbl_mike;

'
-
x

SQL> SELECT * FROM test;

no rows selected

Here's a trigger and, finally, testing: inserting a record into Scott's table will automatically insert another one into Mike's table:
SQL> CREATE OR REPLACE TRIGGER trg_ins_mike
  2    AFTER INSERT ON test
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO test@dbl_mike (col) VALUES (:new.col);
  6  END;
  7  /

Trigger created.

SQL> INSERT INTO test (col) VALUES ('Littlefoot');

1 row created.

SQL> SELECT * FROM test;

COL
--------------------------------------------------
Littlefoot

SQL> CONNECT mike/lion@ora10lp
Connected.
SQL> SELECT * FROM test;

COL
--------------------------------------------------
Littlefoot

SQL>

Unless I misunderstood what you are saying, this *might* be what you are looking for (with certain modification).
Previous Topic: use off execute immediate
Next Topic: JOIN
Goto Forum:
  


Current Time: Mon Feb 17 23:21:46 CST 2025