[Dbix-class] Problem with DBIC::PK::Auto::Oracle and compositetriggers

[James FitzGibbon]

The biggest problem I have with separate triggers (in Oracle) is that the
firing order is undefined for triggers of the same type.  So if one BEFORE
INSERT trigger populates your primary key and another BEFORE INSERT trigger
inserts a record in an audit table, you have no guarantee that your audit
record will contain your primary key value.  Which would be, well, bad.  8-)

Oracle recommends one large trigger to ensure that the order is the one you
want, and while you can make that trigger call out to separate PL/SQL
procedures for the purposes of keeping things "clean", there is a whole bag
of other problems that come along with (such as the pseudo-collections 'new'
and 'old' which can't be passed as a collection to another procedure).

It's all doable, just proportionately ugly.

I'll probably stick with the subclass "LIKE '%INSERT%'" solution.

Thanks

-----Original Message-----
From: dbix-class-bounces at lists.rawmode.org
[mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of Brandon Black
Sent: Tuesday, January 03, 2006 2:41 PM
To: dbix-class at lists.rawmode.org
Subject: Re: [Dbix-class] Problem with DBIC::PK::Auto::Oracle and
compositetriggers

On 1/3/06, James FitzGibbon <jfitzgibbon at primustel.ca> wrote:
> [...]
> A cursory examination of the Oracle docs doesn't suggest a place in the
> dictionary that lets you see a normalized view of the triggering events.
> So, I figure that the solution is one of:
>
> - change my trigger auto-generation to create separate triggers (or at
least
> separate triggers for sequence creation
>
> - modify PK::Auto::Oracle to use "LIKE '%INSERT%'" instead of "= 'INSERT'"
> to find the trigger body (either in the proper dist, or if this is a "just
> me" thing, a private subclass that overrides _get_autoinc_seq)
>
> Any thoughts?
>

I would split those triggers up, unless there's a good reason not to. 
If you have a bunch of huge "INSERT OR UPDATE OR DELETE" triggers, and
they all have a top-level switch that says "IF INSERTING ... IF
UPDATING ...", then it seems they'd be better off (cleaner) as
seperate triggers anyways.

The PK::Auto code for most backends will probably always be a bit
heuristic in nature, and there will probably always be odd cases that
it cannot figure out without hints from you.  I think perhaps the most
productive change to PK::Auto and friends would be to make it easier
and more obvious for you to override _get_autoinc_seq logic for your
particular database class as a whole (as opposed to per-table).

Just my $0.02,
-- Brandon

_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.11/219 - Release Date: 1/2/2006
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.11/219 - Release Date: 1/2/2006
 



-- 
----------------------------------------------------------------------------
This electronic message contains information from Primus Telecommunications
Canada Inc. ("PRIMUS") , which may be legally privileged and confidential.
The information is intended to be for the use of the individual(s) or entity
named above. If you are not the intended recipient, be aware that any
disclosure, copying, distribution or use of the contents of this information
is prohibited. If you have received this electronic message in error, please
notify us by telephone or e-mail (to the number or address above)
immediately. Any views, opinions or advice expressed in this electronic
message are not necessarily the views, opinions or advice of PRIMUS.
It is the responsibility of the recipient to ensure that
any attachments are virus free and PRIMUS bears no responsibility
for any loss or damage arising in any way from the use
thereof.The term "PRIMUS" includes its affiliates.
----------------------------------------------------------------------------
Pour la version en français de ce message, veuillez voir
 http://www.primustel.ca/fr/legal/cs.htm
----------------------------------------------------------------------------