How do you use rails with (triggers and Oracle)?

Rails
1

I have an existing database in Oracle. All the tables have triggers
that insert the next value from the table sequence into the id column.

I have told rails about the sequence using set_sequence_name.

The problem occurs when rails trys to insert a new row. It grabs the
nextval from the sequence and uses that in its INSERT sql, BUT then the
oracle trigger trigger automatically takes over (as it should) and grabs
the next value from the sequence and forces it into the inserted row.
This results in rails having the wrong id for that record. The id that
rails has is minus one from what it should be.

My current solution is to minus one from the model’s id when i need to
use it subsequently. This is not an ideal solution and is a real ball
ache.

Is there any way around this problem with ActiveRecord? or is there a
very clever way round it?

Any advice would be useful.

Thanks

Chris R.

2

Chris R. wrote:

I have an existing database in Oracle. All the tables have triggers
that insert the next value from the table sequence into the id column.

I have told rails about the sequence using set_sequence_name.

The problem occurs when rails trys to insert a new row. It grabs the
nextval from the sequence and uses that in its INSERT sql, BUT then the
oracle trigger trigger automatically takes over (as it should) and grabs
the next value from the sequence and forces it into the inserted row.
This results in rails having the wrong id for that record. The id that
rails has is minus one from what it should be.

My current solution is to minus one from the model’s id when i need to
use it subsequently. This is not an ideal solution and is a real ball
ache.

Is there any way around this problem with ActiveRecord? or is there a
very clever way round it?

Any advice would be useful.

Thanks

Chris R.

I have the same problem. I’m using the oracle enhanced adapter v 1.2.4.
The adapter does look for a present trigger with the default trigger
name (i.e. <table_name>_PKT), but I don’t use default names so he
doesn’t find it.

My current solution is to simply override the method
has_primary_key_trigger? that looks for the trigger:

module ActiveRecord
  module ConnectionAdapters
    class OracleEnhancedAdapter < AbstractAdapter
      def has_primary_key_trigger?(table_name, owner = nil, 
desc_table_name = nil, db_link = nil)
        true
      end
    end
  end
end

As I always use triggers, this works for me, but I know it isn’t the
best solution.

Regards,

Denzel