数据库触发器更新列后如何进行查询？

Question

I have a sequence of insert, after insert trigger update, and query operations and my problem is that the data received from the query does not reflect the updates performed by the trigger (even though the data in the database is indeed updated).

I'm using PHP, PostgreSQL and Propel 1.6 ORM (although this problem might be database and ORM agnostic).

I have the following sequence:

1. Use AJAX to insert a new row (a vote) in the "book_vote" table (with columns: 'book_id', 'book_score', 'voter_id').

2. Have a PostgreSQL after insert trigger to update the corresponding book "vote_count" and "average_score" columns in the "book" table.

3. Make a query to get the new "vote_count" and "average_score" data for the "book" and send that data back to the client with AJAX (to display updated values after the vote).

This all happens within the same PHP session, and my problem is that I do not get the updated "book" values in the AJAX response. It seems like the query is performed before the database trigger is performed. Is there any way to ensure the query happens after the database trigger?

Answer 1

It seems to me that you are doing a save on a Propel object, and you have an external trigger that modifies that row further. You need to be able to tell Propel that the object needs refreshing immediately after the insert/update... and thankfully Propel supports that directly. In your table element in your schema, do this:

<table name="book_vote" reloadOnInsert="true" reloadOnUpdate="true">

You can use either or both of the reload statements, depending on requirements (in your case you'll probably just want the insert one). From there, just rebuild your model and it should work fine.More details here.

Addendum: as per discussion, the issue appears to be that you have already loaded the foreign row that you wish to reload, and as such it is being retrieved from the instance pool rather than the database. You've found that BookPeer::clearInstancePool() solved it for you - great! For bonus points, see if you can remove items from the pool individually - it is probably more efficient to allow the pool to run normally and to remove items one at a time, rather than to clear the pool for a whole table. Let us know if this is possible!