Re: 10g: BIU trigger "plugins"
Date: Wed, 16 Feb 2011 04:59:42 -0800 (PST)
Message-ID: <38d3ff95-115a-4da8-8943-290ec2ec8e8c_at_q14g2000vbf.googlegroups.com>
On Feb 15, 4:10 pm, Frank Bergemann <FBergem..._at_web.de> wrote:
> 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.
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 Received on Wed Feb 16 2011 - 06:59:42 CST