Pragma Autonomous_transaction [message #607727] |
Tue, 11 February 2014 08:23 |
|
vaibhav15211
Messages: 38 Registered: August 2012 Location: Hyderabad
|
Member |
|
|
Hello,
I am having an on insert trigger TR1 on TB1. A procedure P1 is called from this TR1 which inserts data from TB1 into TB2 after validations.For the validations purpose the TB1 is again referred in the Procedure and thus it was throwing table mutating error. To handle this I made the procedure as pragma autonomous_transaction.
Now the issue is that if I insert 1 line I1, in TB1 then the results in TB2 are reflected after I insert the second line I2 in T1 and so on and so forth.
I am not sure why this is happening.
Please suggest.
Thanks,
Vaibhav
|
|
|
|
|
|
|
Re: Pragma Autonomous_transaction [message #607736 is a reply to message #607727] |
Tue, 11 February 2014 08:44 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
If those validations only involve the newly inserted row, do not 'refer' TB1 in the procedure and pass all used column values into P1 as parameters.
If the SELECT inside P1 queries another row(s) of TB1, you have serial design issue (most probably the table is not in the 1st Normal Form), so the data model should be changed.
|
|
|
|
Re: Pragma Autonomous_transaction [message #607739 is a reply to message #607733] |
Tue, 11 February 2014 09:03 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
vaibhav15211 wrote on Tue, 11 February 2014 15:40That's so nice of you to point out my mistake. But what is the mistake ,if you can point out will be really helpful.
It is in the code you didn't post.
|
|
|