| Subject | RE: [firebird-support] Re: multiple inserts in trigger |
|---|---|
| Author | Helen Borrie |
| Post date | 2007-07-12T00:09:35Z |
At 12:51 AM 12/07/2007, you wrote:
on the work order table during the execution of a trigger on the work
order table then that certainly doesn't make sense.
I'm interested to know how you're testing whether the updates
work. If you are watching the audit tables using some tool, in the
expectation that you should see the results of triggers as soon as
they fire, then you are barking up the wrong tree. The work of
triggers does not appear to outside transactions until the whole of
the transaction commits. By the same token, any work performed by a
trigger will disappear if the transaction is rolled back.
Under some transaction settings, transaction A cannot insert into
table X if transaction B - the one that you are running to monitor
the effects, for example - is a read/write transaction that has an
open cursor on a set that would be affected by the insert, e.g.
select * from do_auditdtl.
If your transaction A is in WAIT lock resolution, it won't
except. It will wait ("hang") until transaction B commits. You're
likely to compound the bottlenecks if you have multiple users all
trying to do the same thing and someone has a never-ending read/write
transaction running to monitor the log table.
Another "between the lines" thing that isn't apparent from the info
you have provided so far is that your DO_AUDITDTL table might have a
dependency on the work order table. Does it? e.g. a foreign key on ORDERNUM?
./heLen
>Ok, I broke each test off into a separate trigger and it works fine. ThisNo, not yet...but if you're saying that you are trying to do a SELECT
>does not make sense to me since it should be the same difference. In single
>file many tests I am doing a After Update so the second fire of the UPDATE
>trigger for the second field change should test the first value as no change
>and thus skip it. I tried doing one as an insert command and the other as a
>select command reducing the procedure just down to two tests and it still
>hangs. When I do one at a time it inserts the record when I do two changes
>with two tests in one trigger neither update the audit file.
>
>Does this make sense to anyone?
on the work order table during the execution of a trigger on the work
order table then that certainly doesn't make sense.
I'm interested to know how you're testing whether the updates
work. If you are watching the audit tables using some tool, in the
expectation that you should see the results of triggers as soon as
they fire, then you are barking up the wrong tree. The work of
triggers does not appear to outside transactions until the whole of
the transaction commits. By the same token, any work performed by a
trigger will disappear if the transaction is rolled back.
Under some transaction settings, transaction A cannot insert into
table X if transaction B - the one that you are running to monitor
the effects, for example - is a read/write transaction that has an
open cursor on a set that would be affected by the insert, e.g.
select * from do_auditdtl.
If your transaction A is in WAIT lock resolution, it won't
except. It will wait ("hang") until transaction B commits. You're
likely to compound the bottlenecks if you have multiple users all
trying to do the same thing and someone has a never-ending read/write
transaction running to monitor the log table.
Another "between the lines" thing that isn't apparent from the info
you have provided so far is that your DO_AUDITDTL table might have a
dependency on the work order table. Does it? e.g. a foreign key on ORDERNUM?
./heLen