10g: BIU trigger "plugins"
Frank Bergemann
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
Frank Bergemann
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 :old conditions.
Or does oracle take care for sequential execution of trigger for same
record?
- thanks!
rgds,
Frank
Frank Bergemann
found the response here:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/usingpe.htm#CACEJACE
It says:
"Parallel DML operations cannot be done on tables with triggers."
rgds,
Frank
Randolf Geist
You can see the description of what is happening to your code here, at
AskTom:
http://tkyte.blogspot.com/2010/04/that-old-restart-problem-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-Practices-Database-Administration/dp/1430226684
Randolf Geist
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.
joel garry
> On Feb 16, 7:34 am, Frank Bergemann <FBergem...@web.de> wrote:
>
>
>
> > 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 :old conditions.
> > Or does oracle take care for sequential execution of trigger for same
> > record?
>
> > - thanks!
>
> > rgds,
> > Frank
>
> 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.
And Tom of course addresses that in detail, too:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID: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