4 Replies Latest reply: Feb 8, 2013 11:40 AM by rp0428 RSS

    Need to call a procedure after an insertion on a table is commited

    BLKR
      I have a table say A which contains a list of tables and owner. I want an procedure to be fired which would apply a policy on those newly inserted table names in table A.
      Please suggest. I tried using After trigger but the procedure gets called immediately after Insert and doesn't wait for an commit to be provided. i need it to fired after an commit is provided for that session else if commit is not performed it should not fire the procedure.

      Please suggest how should i move ahead.

      Thanks in advance.
        • 1. Re: Need to call a procedure after an insertion on a table is commited
          bencol
          You could let the trigger set the procedure to run "immediately" using dbms_job. As dbms_job is transactional, the procedure will not be called until the commit is issued
          • 2. Re: Need to call a procedure after an insertion on a table is commited
            BLKR
            But using dbms_job could cause trouble too if large number of insert is performed. Isn't it said that its not advisable to use it in a trigger. Appreciate your response, can you help me find out how optimum values should b for using dbms_job and if there could be some other way apart from trigger.

            Thanks for your time.
            • 3. Re: Need to call a procedure after an insertion on a table is commited
              rp0428
              >
              But using dbms_job could cause trouble too if large number of insert is performed. Isn't it said that its not advisable to use it in a trigger. Appreciate your response, can you help me find out how optimum values should b for using dbms_job and if there could be some other way apart from trigger.
              >
              We can't help until you make up your mind what your requirement really is.

              Do you want to execute it after each insert is committed or not? If so then it is irrelevant that a 'large number of insert is performed'. You can't have it both ways.
              • 4. Re: Need to call a procedure after an insertion on a table is commited
                bencol
                I haven't tested this, but I do not think there will be much overhead calling the procedure via dbms_job, compared to without it. But might have issues if you use dbms_job for other processes and want different values for job_queue_procesess (the number of jobs that can run concurrently).

                Another way would be to prevent DML on table A from application user and only allow updates via a stored procedure (which the appliation user can execute), then you have control over what is involved in the business process of the addition of data.

                Ben