i have a problem for "before insert or update" trigger.
Currently this trigger records old table X record in dedicated X_HIST
table (same structure plus sequence counter).
This is done with individual SQL insert into X_HIST table.
I wanted to tune this. So instead of individual SQL insert i invoke a
some_pkg.PushEntry(...) function.
pl/sql package 'some_pkg' manages a collection to buffer given X
records.
When all my stuff is done, then i invoke a some_pkg.InsertEntries() to
do a bulk insert.
But for this i detect duplicates in the collection!
I don't know why there are these duplicates.

Do triggers "not play niceley" with pl/sql collections for this?
It feels, like there are multiple invocations of trigger for the same
X record.
And it doesn't harm if i use the individual SQL insert into X_HIST
table.
But it's a problem if i try to delay SQL insert for bulk operation.

- many thanks!

rgds,
Frank

Hi,

are triggers executed in parallel for parallel DML?
What happens, if the same record is updated _twice_ in a bulk update?
Something like this:
forall i reloads.first..reloads.last
update account set money = money + reloads(i).amount where
account.id = reloads.acct_id;

Can it happen, that two "trigger before insert or update on account
for each row" are executing in parallel for the same record?
- if acct_id = X is there multiple times in table reloads.

So they will find the same ld conditions.
Or does oracle take care for sequential execution of trigger for same
record?

- thanks!

rgds,
Frank

On 16 Feb., 07:34, Frank Bergemann <FBergem... (AT) web (DOT) de> wrote:
found the response here:
http://download.oracle.com/docs/cd/B...e.htm#CACEJACE
It says:
"Parallel DML operations cannot be done on tables with triggers."

rgds,
Frank

On Feb 15, 4:10*pm, Frank Bergemann <FBergem... (AT) web (DOT) de> wrote:
You can see the description of what is happening to your code here, at
AskTom:

http://tkyte.blogspot.com/2010/04/th...lem-again.html

If you follow the link to AskTom in his post you'll also find links to
his old three-post series that describes the issue in detail.

In a nutshell - this is not going to work this way. You need either a
completely different approach or at least handle the potential
duplicate issue in your particular case (there might be other side
effects I'm not thinking of now, though, depending on what exactly you
do).

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On Feb 16, 7:34*am, Frank Bergemann <FBergem... (AT) web (DOT) de> wrote:
Not directly answering your question but FORALL is not performing
parallel DML, it is merely doing a bulk operation that minimizes the
overhead of single row operations. I'm not aware of it being able to
run in parallel DML mode.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On Feb 16, 5:08*am, Randolf Geist <mah... (AT) web (DOT) de> wrote:
And Tom of course addresses that in detail, too:
http://asktom.oracle.com/pls/asktom/...17483288166654

He also shows in passing that a loop is simply a goto :-)

jg
--
@home.com is bogus.
"I reckon another 5 years and Tb dbs are gonna be as common as tea in
a pot. We better be ready... " - Noons, Nov, 2004