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 Richards -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Denzel De rocker
2010-May-03 13:50 UTC
Re: How do you use rails with (triggers and Oracle)?
Chris Richards 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 RichardsI 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: [code] 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 [/code] As I always use triggers, this works for me, but I know it isn''t the best solution. Regards, Denzel -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.