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

[James FitzGibbon]

I recently moved to creating large composite triggers using TT2 rather than
having one trigger for sequences, one for validation, etc.  One side effect
of this is that my sequence generation is contained as part of a larger
trigger that fires for INSERT, UPDATE and DELETE events:

CREATE OR REPLACE TRIGGER
    job_trig
BEFORE INSERT OR UPDATE OR DELETE ON
    job
FOR EACH ROW
BEGIN
    IF INSERTING THEN ...
        IF( :new.id IS NOT NULL ) THEN ...
        IF( :new.status_class IS NOT NULL ) THEN ...
        SELECT main_seq.NEXTVAL ...
        SELECT DECODE(:new.status, ... ) INTO :new.status_class ...
        INSERT INTO job_audit ...

This has broken DBIC::PK::Auto::Oracle, because it doesn't find the trigger
in order to grep out which sequence is used.  The SQL it uses looks like
this:

  my $sql = qq{
    SELECT trigger_body FROM ALL_TRIGGERS t
    WHERE t.table_name = ?
    AND t.triggering_event = 'INSERT'
    AND t.status = 'ENABLED'
  };

But in this case, t.triggering_event is the full string 'INSERT OR UPDATE OR
DELETE':

SQL> select trigger_name, triggering_event from all_triggers where
table_name = 'JOB';

TRIGGER_NAME
------------------------------
TRIGGERING_EVENT
----------------------------------------------------------------------------
----
JOB_TRIG
INSERT OR UPDATE OR DELETE

SQL>

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?

-- 
j.
 
James FitzGibbon
Systems Developer, Primus Telecommunications Canada Inc.
416.644.6111

-- 
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
----------------------------------------------------------------------------