[Dbix-class] Oracle: Finding a trigger which belongs to another table owner

[sindharta_tanuwijaya at yahoo.co.jp]

Hi, 
 
I'd like to share what we've edited in "DBI::Oracle::Generic.pm" and to ask if there is any better solution to our problem.

We are making a program using Catalyst and one of the  database backends that we are using is an Oracle Server. Previously we had a little problem with "DBIx::Class::Storage::DBI::Oracle" because we  needed to do record insertions into tables of another schema, say for example: 
 "another_schema.table_name", and DBI::Oracle can't find the trigger  required to auto increment its id (we use trigger to auto increment  primary ids). 
 
After checking around a little bit, we discovered that  "DBIx::Class::Storage::DBI::Oracle" usually can discover our trigger automatically, if we use this  line of code in the schema class. 
 __PACKAGE__->table( table_sample); 
  but unfortunately, it can't find the trigger if we use this line of code instead:
 __PACKAGE__->table( owner_sample.table_sample); 
  
So, we added our own code into "DBIx/Class/Storage/DBI/Oracle/Generic.pm" so that it will try to discover trigger by using the table name and table owner automatically, if it can't find the trigger by using only table name itself, and it worked. Our additional code is listed below:

...
 ...
  my $sql_owner = q{
    SELECT trigger_body, t.table_owner FROM ALL_TRIGGERS t
    WHERE concat(concat(t.table_owner,'.'),t.table_name) = ?
    AND t.triggering_event = 'INSERT'
    AND t.status = 'ENABLED'
  };
...
...
 
Please let me know what you think.

Regards,

Sindharta

 

 
---------------------------------
Easy + Joy + Powerful = Yahoo! Bookmarks x Toolbar
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080107/61bec6df/attachment.htm