| Row Level trigger [message #686799] |
Wed, 11 January 2023 03:53  |
 |
indupriyav_2023
Messages: 3
Registered: January 2023
|
Junior Member |
|
|
Hi,
We have a before insert row level trigger on mytable.
On a high level we perform the following.
Inside the trigger, we perform a validation by calling a pragma autonomous transaction function where we compare :new fields with mytable data and return with 1 or 0 based on validation.
It works from UI.
If the following stmt has 2 recrods the trigger is not working during bulk insert.
insert into mytable
select from import_mytable
But when I insert a recrod , commit first recrod and then perform insert of 2nd record the function valiadation error works fine.
Is row level before insert trigger supposed to work on committed data from UI but not for inserting multiple recrods from excel sheet where they commit at the end.
Please advice.
Thanks!!!
|
|
|
|
| Re: Row Level trigger [message #686800 is a reply to message #686799] |
Wed, 11 January 2023 04:09  |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
You have a very big design problem, in fact it will never work.
Quote:we perform a validation by calling a pragma autonomous transaction function where we compare :new fields with mytable data and return with 1 or 0 based on validation.
This is the flaw.
An autonomous transaction is another transaction which does not see uncommitted data from other transaction and then from the original transaction that fires the trigger.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
