Triggers and commitment control?

[T.Grove]

I'm wondering whether the better question might not be to control the firing of the trigger to file only after the program has committed the records. Other than that, I'm stumped too. I'd love to hear this answer as well....

[Guest.Visitor]

Triggers and commitment control?

As far as i know, a trigger is an extension of database code. So a trigger can not decide whether to commit or rollback. The changes made by a trigger to db-recoverable resources are included in eventual commit/rollback by end-user. If you want to update non-recoverable resources (e.g. dataqueue) by trigger, this is not a good way. You have to work with committed indormation in database-journals. Herman Vierendeels

[bhatch]

Triggers and commitment control?

Patrick, are your data queues under commitment control as well? What OS level are you running? Are you using external triggers? My SQL triggers run in the same commitment control scope as the triggering action, so if the parent action is rolled back then actions performed by the triggers are also rolled back. Our triggers are touching database files exclusively though, so maybe actions to data queues just don't rollback in the same way. I think when a rollback is in progress then further trigger action is suspended during that transaction. I think that explains why your second trigger isn't firing. Instead of relying on a second trigger to remove the data from the data queue, you could change the program processing the queue to look at the journal for the primary file (the file that caused the trigger to fire) to see if the item in the queue was rolled back or committed. (sorry for the broken post above, MC should rally add a preview before post)

[R.Daugherty]

Triggers and commitment control?

They did have a Preview, which actually displayed the post correctly. Then when you Submit it wiped out the whitespace. I posted awhile back suggesting that Preview code be used for normal display of posts. We may be on autopilot in never never land though, I don't know. They also have a Delete button which sort of works. It deletes the post but leaves your name in the list of posts and a link to the aforementioned never never land. In addition to your and other responses, a design to delete a dataq entry upon rollback is about as shaky as it gets. What, the dataq processing is constrained by the COMMIT boundary? If one is using a trigger to send off for further processing, write it to a file under COMMIT and have the other job processing it read for update. The record will be locked until the COMMIT takes place, or will be rolled back if that occurs. When a read is successful, that program could then write it to the dataq, so just an intermediate step to add to current design to only process committed records into the dataq. rd

[Guest.Visitor]

Triggers and commitment control?

I have a file with a trigger that fires after an insert/update/delete. The trigger program write the before and after images to a data queue for further processing, in an attempt to return back to the calling program as quickly as possible. If there is a rollback under commitment control a second trigger does not fire. As a result the trigger program sends out information which has since been erased by the subsequent rollback. Does anyone know a way to make the trigger fire on the rollback?

[bhatch]

Triggers and commitment control?

Ralph, I'm not sure if I followed your post fully. If you're suggesting that he change the trigger to write to a file instead of the data queue? If that's the case, then I think the goal of the original poster was to avoid the latency of normal file IO in the trigger by writing to a data queue instead of putting. I suppose data queues really are faster, but at the expense of niceties like commitment control. My impression from his post was that he needs the transaction that causes the trigger to fire to complete as quickly as possible, while whatever happens with the stuff in the data queue can happen at a more liesurely pace. My suggestion wasn't to delete the stuff in the data queue, but for the job processing the queue to make sure that each queue item it processes is a valid, commited record. That would be additional overhead, but maybe that's ok. I agree that trying to delete the stuff from the queue on a rollback is pretty hopeless. I don't think there's anyway to make it happen reliably in the same job stream without incurring a processing penalty in the originating transaction. I think that he will definitely need to deal with the non-committed data queue records on the backend, in whatever job processes that queue. Do you know of anyway to get the transaction identification while it's in process? If you could write that into the data queue in addition to the row data then it would be pretty easy to look at the journal to see if it was committed or rolled back. I could be completely off base on my understanding of your post. If that's the case, can you explain further your suggestion? I'm very interested in this topic as it's not far from the kind of problems I may eventually deal with here. (and thanks for the heads up about the delete button. i'm a mc forums newbie and didn't even notice it)