I have two oracle 11g databases. I have a table TASKS in DB1(in a schema) and another table ORDERS in DB2(in a schema). Whenever a new order with(OrderID and OrderStatus) is created in ORDERS table i need to create a new task in TASKS table(with TaskID as OrderID and TaskStatus as OrderStatus). When a user updates TaskStatus in TASKS table Order Status for its order in ORDERS table should get updated. I have a DBLink created in DB1 to access ORDER table in DB2.
To implement this requirement am planning to have a two triggers. First trigger on ORDERS table to insert a new Task in TASKS table whenever a new order is created. Second trigger on TASKS table to update Order Status in ORDERS table whenever there is a task status change in TASKS table.
My Concern is this trigger solution works only if two DBs are up. Whenever DB1 is down and a new order is created in DB2 this solutions fails to insert a new Task in DB1. I think instead of creating First trigger on ORDERS table we can create this trigger on a VIEW created in DB1 for ORDERS table in DB2. This solution to create VIEW in DB1 solves the problem of inserting a new TASK in TASKS table when DB1 is down. I think this solution of creating a view on TASKS table for task status change will not work as both view and table are in same DB1.
Is there any better approach for this problem? Please help.
