Subject:XML/SQL update more tables Author:Robert Hlinka Date:15 Sep 2006 07:36 AM
I have a question if it is possible to do INSERT/UPDATE to more than one table at once using your SQL/XML functionality.
Here is situation description:
I have 2 tables in DB.
T_CUST (SID INTEGER PRIMARY KEY, NAME VARCHAR2(20))
T_ADDR (SID INTEGER PRIMARY KEY, STREET VARCHAR2(40), CUST_SID INTEGER
FOREIGN KEY (CUST_SID)
And I have XML document like this:
Is is possible to INSERT/UPDATE both tables at once using UPDATE tab in DBtoXML ??
How should the statement look like?
If not, is there any other way how to INSERT data from XML document to DB tables using Stylus Studio?
Thank you for any advice.
Reverse operation is possible by this statemnt
FROM T_ADDR a
FROM T_CUST t
The problem you are posing doesn't have an easy solution.
Be able to update multiple tables from a single update statement when identity columns are involved is very difficult because the transaction isolation prevents to see the changes until they are committed.
If you can't see the changes you can't update the relationships, so for instance you are not able to set the order.customerID to the customer.ID indentity you are creating.
You may try to configure a trigger on update or store the aggregated data in a temporary table then use a store procedure to normalize it back.