Hi Tom,
Yes, I can update it from sqlplus and it works. But, when I post another reply to the thread, the value reverts back to the date of the first post. This is very odd! I have verified that the value of posting_date is correct before the update; that is, it's set to sysdate.
I just went through forums_messages.new(), adding in raise statements after each code block to check the value of last_child_post. And sure enough, this
update forums_messages
set last_child_post = posting_date
where message_id = v_root_message_id;
is where it reverts. There's nothing wrong with the data:
J9c: v_root_message_id = 51542, last_child_post = 09-10-2003 10:53:36, posting_date = 09-10-2003 11:39:23
It's using the right message_id, and the posting_date is set to sysdate. But the value of last_child_post has reverted back to the date of the first post (this data is from after the update).
Since it seems at this point like it must be trigger related, I tried dropping forums_mess_insert_tr, the only trigger in forums, and sure enough the problem went away:
J9c: v_root_message_id = , last_child_post = 09-10-2003 11:22:14, posting_date = 09-10-2003 11:57:22
This time, the value of last_child_post is still what I had set it to for testing purposes.
But this is very bizarre! The code being executed in that trigger is
else
select nvl(tree_sortkey, ''), max_child_sortkey
into v_parent_sortkey, v_max_child_sortkey
from forums_messages
where message_id = :new.parent_id
for update of max_child_sortkey;
v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
update forums_messages
set max_child_sortkey = v_max_child_sortkey
where message_id = :new.parent_id;
end if;
:new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;
Where :new.parent_id is set to the message_id of the message being replied to (the most recent in the thread). There is nothing here that should affect the value of last_child_post! Especially since the parent message is not the first message in the thread, which is where last_child_post is set.
I should also mention that I have another site that is exhibiting the same problem, so it's not just this one. They are both Oracle sites, though that "shouldn't" matter.
Does this ring any bells for anyone?
