Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    New Zealand
    Posts
    2

    Unanswered: Trigger question

    When a row is inserted into table A I need to insert another row into table B based on a couple of columns in A.

    I want to add an AFTER INSERT trigger on A that calls a java stored proc to calculate the new row in B based on the columns from A. Problem is that the java can take a long time (a few seconds) and table A can receive many inserts per second. It is fine for B to lag behind A, just as long as "eventually" all the trigger invocations complete.

    Will the trigger on A prevent subsequent inserts on A until a trigger invocation is completed? i.e. lock A.

    Is what I'm planning to do a good idea, or is there a better way? The main problem is that the business logic for calculating the row in B is quite time consuming.

    Dale

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Trigger question

    Originally posted by dale77
    When a row is inserted into table A I need to insert another row into table B based on a couple of columns in A.

    I want to add an AFTER INSERT trigger on A that calls a java stored proc to calculate the new row in B based on the columns from A. Problem is that the java can take a long time (a few seconds) and table A can receive many inserts per second. It is fine for B to lag behind A, just as long as "eventually" all the trigger invocations complete.

    Will the trigger on A prevent subsequent inserts on A until a trigger invocation is completed? i.e. lock A.

    Is what I'm planning to do a good idea, or is there a better way? The main problem is that the business logic for calculating the row in B is quite time consuming.

    Dale
    Hi,
    The question that you have put is more business driven than technology
    driven. if it is so very slow, then lag the other table by 2 or 3 hours
    (or what ever suits your business) and run a batch program that does
    the validation.

    hope it helps.
    nn

  3. #3
    Join Date
    Apr 2003
    Location
    New Zealand
    Posts
    2
    There are two parts to my question:

    1. Will table A be unable to handle other inserts while the AFTER INSERT trigger is waiting for a response from my java stored proc? i.e. given the following sequence of events:

    a) First INSERT on A
    b) First AFTER INSERT TRIGGER begins...
    c) Second INSERT on A

    What happens to the second insert on A, does it succeed immediately causing a second AFTER INSERT invocation, or is it blocked until the first AFTER INSERT completes?

    2. Is there a better way to do it?

    Thanks for your answer to 2. In fact we want table B to be updated ASAP after the row is inserted in table A, but we also don't want inserts on A bottlenecked by this single long running process.

    Dale

Posting Permissions

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