Prevent parallel exection of procedure

Question

I have a table trigger, which calls a procedure when the status change from 2 to 3. The procedure check if the whole group of data(group_id) is in status 3 and then perform some actions.

But now I'm facing the problem that when I set the whole group of data in status 3 at the same time, the procedure get called multiple times and perform this actions multiple times. How can I prevent his? For example with locks

Here is my procedure query:

     SELECT COUNT(*)
     INTO nResult
     FROM ticket
     WHERE group_id = nGroupId
     AND statusid BETWEEN 0 AND 2;

     /* If not all tickets of group in status 3, no action required */
     IF nResult != 0 THEN
        RETURN;
     END IF;

And this is my trigger:

IF (:NEW.STATUSID = 3 AND :OLD.STATUSID = 2) THEN 
    myprocedure(:NEW.group_id);
END IF;

Answer 1

You probably have a row level trigger, that is fired every time a row is updated; for example:

SQL> create table trigger_table(status number);

Table created.

SQL> insert into trigger_table values (1);

1 row created.

SQL> insert into trigger_table values (2);

1 row created.

SQL> insert into trigger_table values (3);

1 row created.

SQL> create trigger update_trigger
  2    after update on trigger_table
  3    for each row  /* ROW LEVEL */
  4  begin
  5      dbms_output.put_line('change');
  6  end;
  7  /

Trigger created.

SQL> set serveroutput on
SQL> update trigger_table set status = 1;
change
change
change

3 rows updated.

You need a table level trigger, fired after every update statement:

SQL> create or replace trigger update_trigger
  2    after update on trigger_table
  3  begin
  4      dbms_output.put_line('change');
  5  end;
  6  /

Trigger created.

SQL> update trigger_table set status = 1;
change

3 rows updated.

Here you find something more.

As rightly observed by Nicholas Krasnov, in this kind of trigger, considering a set of rows and not a single one, you have not the :new or :old values. A way to get your needs could be the following, but it's a tricky solution and I'd check it carefully before using in a production environment.

You could create a semaphore table to know if you have to fire the trigger or not, then use two triggers, one at row level, BEFORE update, and one at table level, AFTER update; the row level one checks the values and updates the semaphore table while the table level one, fired after the update, reads the semaphore, calls your procedure, if necessary, then resets the semaphore. For example:

SQL> create table trigger_table(status number);

Table created.

SQL> insert into trigger_table values (1);

1 row created.

SQL> insert into trigger_table values (2);

1 row created.

SQL> insert into trigger_table values (3);

1 row created.

SQL> create table checkChange (fire varchar2(3));

Table created.

SQL> insert into  checkChange values ('NO');

1 row created.

SQL> create or replace trigger before_update_trigger
  2    before update on trigger_table
  3    for each row  /* ROW LEVEL */
  4  begin
  5      if :new.status = 3 and :old.status = 2 then
  6          update checkChange set fire = 'YES';
  7      end if;
  8  end;
  9  /

Trigger created.

SQL> create or replace trigger after_update_trigger
  2    after update on trigger_table
  3  declare
  4    vFire  varchar2(3);
  5  begin
  6      select fire
  7      into vFire
  8      from checkChange;
  9      if vFire = 'YES' then
 10          dbms_output.put_line('change');
 11          update checkChange set fire = 'NO';
 12      end if;
 13  end;
 14  /

Trigger created.

SQL> update trigger_table set status = 2;

3 rows updated.

SQL> update trigger_table set status = 3;
change

3 rows updated.

SQL>