On Oct 11, 2009, at 9:32 AM, Nicol van der Merwe wrote:
> Hi All > > I am using sqlsoup and pyodbc to update a couple of rows in the > database. On a number of tables in the database I have triggers that > basically checks keeps a log of changes on certain columns in the > table (inserts changes into another table). My problem is that I am > unable to update values on tables that has this trigger. No error/ > warning is thrown, it just fails silently. When I disable the > triggers the updates work fine. Also, if I run the update statement > directly on the database it works as expected, whether it's run with > or without the triggers. the main issue with your script is that the work you are doing with "connection" and "trans" is meaningless versus the operation you are performing on your objects. SQLSoup uses the ORM so you need to be using the Session to control transactional scope - this is documented here: http://www.sqlalchemy.org/docs/05/session.html . I think for SQLSoup itself its sufficient to say db.commit(). beyond that, if there is truly a different behavior when the trigger exists versus does not, the next meaningful test is to try your use case with a raw pyODBC script. At that point its likely an ODBC related issue. > > Unfortunately disabling the trigger before the update (which makes > the update behave as expected) and re-enabling it afterwards is not > an option as this disables the trigger or ALL connections/sessions. > > Attached is an example of what I am doing that is not working. I > can't figure out why this trigger would cause updates on these > tables to fail. What's even more confusing is that one of the tables > has another trigger (that also does inserts/updates on a different > table) - when I run updates on this table while this trigger is > active, it behaves as expected. > > The above problem also happens if I run the sql through pyodbc - > leading me to believe that the problem might be there. My questions > are the following: > 1) Has anyone else encountered this behaviour? If so - have you > any advice for me? > 2) Is this a bug with pyodbc or sqlalchemy? > 3) Am I doing something wrong? (Is my use of sqlalchemy and > sqlsoup incorrect?) > > > -- > Nicolaas > > > > <triggersstoppingupdate.py> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
