Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    16

    Unanswered: Fire trigger before a commit

    Hi!

    I would like to execute a trigger when for certain events on a table. However I would like it to be fired just before the transaction that does the update is commited, not just before the update occurs.

    Looking at the docs for triggers the only options I see are before or after the actual update.

    Is there some way to accomplish this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Fire trigger before a commit

    Originally posted by umciggy
    Hi!

    I would like to execute a trigger when for certain events on a table. However I would like it to be fired just before the transaction that does the update is commited, not just before the update occurs.

    Looking at the docs for triggers the only options I see are before or after the actual update.

    Is there some way to accomplish this?
    There is no trigger that fires at COMMIT time. However, constraints can be set to DEFERRED so that they are only check at COMMIT time. It may therefore be possible to achieve your goal by a combination of a trigger and a check constraint. Could you post some details of the rule you are trying to enforce?

  3. #3
    Join Date
    Jan 2003
    Posts
    16

    Re: Fire trigger before a commit

    Originally posted by andrewst
    There is no trigger that fires at COMMIT time. However, constraints can be set to DEFERRED so that they are only check at COMMIT time. It may therefore be possible to achieve your goal by a combination of a trigger and a check constraint. Could you post some details of the rule you are trying to enforce?
    I have an invoice table and an invoice_row table. In my application I (1) insert the invoice, (2) insert the invoice_rows and (3) issue a commit. The problem is that when the insert trigger on the invoice table fires, I need to see the invoice_rows.

    Maybe this can be solved by setting the constraint between invoice and invoice_row to 'deffered' and changing the order between steps (1) and (2) ?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Fire trigger before a commit

    Originally posted by umciggy
    I have an invoice table and an invoice_row table. In my application I (1) insert the invoice, (2) insert the invoice_rows and (3) issue a commit. The problem is that when the insert trigger on the invoice table fires, I need to see the invoice_rows.

    Maybe this can be solved by setting the constraint between invoice and invoice_row to 'deffered' and changing the order between steps (1) and (2) ?
    Yes, you could do that. What does the trigger want to do exactly?

  5. #5
    Join Date
    Jan 2003
    Posts
    16

    Re: Fire trigger before a commit

    Originally posted by andrewst
    Yes, you could do that. What does the trigger want to do exactly?
    It calculates a sum based on data in the invoice and invoice_row tables, and then adds that sum to a column of a third table(products.total_order_value).

    I guess this could be done via a regular stored procedure call from the application right after the invoice has been inserted, however the products.total_order_value is re-calculated by triggers whenever invoice or invoice_row are updated. So I thought it would be nice to use triggers for inserts also, just for consistency.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Fire trigger before a commit

    Originally posted by umciggy
    It calculates a sum based on data in the invoice and invoice_row tables, and then adds that sum to a column of a third table(products.total_order_value).

    I guess this could be done via a regular stored procedure call from the application right after the invoice has been inserted, however the products.total_order_value is re-calculated by triggers whenever invoice or invoice_row are updated. So I thought it would be nice to use triggers for inserts also, just for consistency.
    Another possibility would be to put triggers on the invoice_row table as well as (or instead of) in the invoice table, e.g.

    create trigger invoice_row_aiudr after insert or update or delete on invoice_row
    for each row
    begin
    update products
    set total_order_value = total_order_value + NVL(:NEW.amount,0) - NVL(:OLD.amount,0)
    where prod_id = NVL(:NEW.prod_id,:OLD.prod_id);
    end;

    You need to be aware that with whatever method you use, maintaining the products.total_order_value column will serialize maintenance of invoice data: 2 users cannot create an invoice_row for the same product at the same time, since the products table is locked by the update. Do you really need to maintain this anyway - can it not be derived as and when required using SUM(invoice_row.amount)?

  7. #7
    Join Date
    Jan 2003
    Posts
    16

    Re: Fire trigger before a commit

    Originally posted by andrewst
    Another possibility would be to put triggers on the invoice_row table as well as (or instead of) in the invoice table, e.g.

    create trigger invoice_row_aiudr after insert or update or delete on invoice_row
    for each row
    begin
    update products
    set total_order_value = total_order_value + NVL(:NEW.amount,0) - NVL(:OLD.amount,0)
    where prod_id = NVL(:NEW.prod_id,:OLD.prod_id);
    end;

    You need to be aware that with whatever method you use, maintaining the products.total_order_value column will serialize maintenance of invoice data: 2 users cannot create an invoice_row for the same product at the same time, since the products table is locked by the update. Do you really need to maintain this anyway - can it not be derived as and when required using SUM(invoice_row.amount)?
    Yes I am aware of that, but the transaction doing the insert is very short so the locking should not be a big problem. The calculation is more complex than a sum (it uses multiplication and depends on status fields in the invoice_row table) so it cannot be solved with a aggregate. It is also critical for our organisation to have very fast and up-to-date access to the products.total_order_value at all times.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Fire trigger before a commit

    Originally posted by umciggy
    Yes I am aware of that, but the transaction doing the insert is very short so the locking should not be a big problem. The calculation is more complex than a sum (it uses multiplication and depends on status fields in the invoice_row table) so it cannot be solved with a aggregate. It is also critical for our organisation to have very fast and up-to-date access to the products.total_order_value at all times.
    Sounds OK then

  9. #9
    Join Date
    Jan 2003
    Posts
    16

    Re: Fire trigger before a commit

    Originally posted by umciggy
    Yes I am aware of that, but the transaction doing the insert is very short so the locking should not be a big problem. The calculation is more complex than a sum (it uses multiplication and depends on status fields in the invoice_row table) so it cannot be solved with a aggregate. It is also critical for our organisation to have very fast and up-to-date access to the products.total_order_value at all times.
    Just had an idea:
    I could maintain a calculated "order_value" column in the invoice table using triggers. And then use a regular sum aggregate if it is reasonably fast every time someone requests a "total_order_value" for a product. Then there would be no serialization for inserts on the invoice table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •