| Multiple Records in single xml with compound trigger [message #624292] |
Sat, 20 September 2014 07:21  |
 |
shannu
Messages: 1
Registered: September 2014
Location: India
|
Junior Member |
|
|
I am working in a java application with Oracle as DataBase.
I have a new requirement of generating customised xml instantaneously on insert /update of records in a table.
I have to even log the details of ids in another table.
Below is the detailed structure of codes available as of now.
/*table 1*/
create table table1(
id NUMBER NOT NULL, -->pk
code VARCHAR2(50) NOT NULL);
/*table 2*/
create table table2(
id NUMBER NOT NULL, --->pk
referred_id NUMBER NOT NULL, --->fk(pk of table1)
action_mode VARCHAR2(20) NOT NULL,
xml_to_be_generated VARCHAR2(1) NOT NULL);
I have compound trigger on each tables.
/*Trigger on Table1*/
Before Statement --> Initialised variables;
After Each Row --> Inserting into type;
After Statement --> Inserting into Table2(Used FORALL to insert the records from table1)
/*Trigger on Table2*/
Before Statement --> Initialised variables;
After Each Row --> Inserting into type;
After Statement --> Call to xml generation Procedure which generates the xml and places in a folder.
I am saving three records from screen in a single transaction/session as below.
insert into table1 values(1,'C1');
insert into table1 values(2,'C2');
insert into table1 values(3,'C3');
commit;
But the records are getting saved individually hence generating 3 xmls.
insert into table1 values(1,'C1'); --> insert into table2 values(1,1,'INSERT','Y');---> xml-1
insert into table1 values(2,'C2'); --> insert into table2 values(2,2,'INSERT','Y');---> xml-2
insert into table1 values(3,'C3'); --> insert into table2 values(3,3,'INSERT','Y');---> xml-3
But my requirement is to generate single xml as
insert into table1 values(1,'C1');
insert into table1 values(2,'C2');
insert into table1 values(3,'C3');--> xml-1
Please share your inputs on this.
|
|
|
|
| Re: Multiple Records in single xml with compound trigger [message #624293 is a reply to message #624292] |
Sat, 20 September 2014 07:36  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
I cannot offer a solution to your problem, other than to say that the whole design looks weird. Doing things like that is not the way a relational engineer would do it. Why two tables? If you must have two, why do you not use a single multitable insert statement, instead of messing about with triggers? Why are you storing the XML doc as a file, rather than in the database?
Generally speaking, if you find that you are doing complex logic in a trigger, you are probably doing something wrong. But I know that Java programmers often use the database in ways for which it was not intended - perhaps someone else can advise.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
