This is fairly easy to test, e.g. by displaying the start and end time in the procedure. (Sample code to demonstrate is below). Within session A, if you do:
update <table> set ...;
update <table> set ...;
... the trigger will fire for each affected row, in series, for the first update as part of that statement; and then it will execute the second update, the trigger will fire for each affected row, in series again. The procedure will only be running once at any time, however may updates you do and however many rows are affected by each one.
Changing to a statement-level trigger would still only have the procedure running once at any time, but it would run fewer times overall if the updates affect more than one row - once per update, rather than once per updated row.
But if you run the second update from session B simultaneously (and it's touching different rows in the table), it would not be affected by the first session, and then you will have the procedure running twice at the same time. It doesn't matter if that's a row-level or statement-level trigger; it'll be running once in each session.
If you wanted to avoid the procedure running twice from different sessions you'd need to implement some kind of locking mechanism, e.g. updating a control flag in another table at the start of the procedure. That would be released when that session commits or rolls back, and the other session will then continue and obtain its own lock.
Having a trigger that calls a procedure that takes that long seems very wrong though. It sounds like you have business and application logic in the wrong place...
Demo code, for running twice in session A:
create table t42 (id number);
insert into t42 values (1);
insert into t42 values (2);
create package p42 as
procedure busy;
end p42;
/
create package body p42 as
procedure busy is
x number;
begin
dbms_output.put_line('Started ' || systimestamp);
for i in 1..200000 loop -- takes about 4s on my system
select 1 into x from dual;
end loop;
dbms_output.put_line('Finished ' || systimestamp);
end busy;
end p42;
/
create trigger trig42
after update on t42
for each row
begin
p42.busy;
end;
/
Then run two updates:
update t42 set id = id + 1;
update t42 set id = id + 1;
Gets output:
2 rows updated.
Started 08-AUG-13 18.17.49.184770000 +01:00
Finished 08-AUG-13 18.17.53.041916000 +01:00
Started 08-AUG-13 18.17.53.042109000 +01:00
Finished 08-AUG-13 18.17.56.841698000 +01:00
2 rows updated.
Started 08-AUG-13 18.17.57.027777000 +01:00
Finished 08-AUG-13 18.18.01.172613000 +01:00
Started 08-AUG-13 18.18.01.172730000 +01:00
Finished 08-AUG-13 18.18.04.963734000 +01:00
The procedure runs four times in total, and timestamps show the procedure execution is serial. If we run ID-specific updates in session A and session B simultaneously, session A sees this:
update t42 set id = id + 1 where id = 1;
1 rows updated.
Started 08-AUG-13 18.21.09.098922000 +01:00
Finished 08-AUG-13 18.21.16.355744000 +01:00
and session B sees this:
update t42 set id = id + 1 where id = 2;
Started 08-AUG-13 18.21.09.500643000 +01:00
Finished 08-AUG-13 18.21.16.204506000 +01:00
1 row updated.
As you can see, the timestamps overlap, so the procedure is running twice concurrently.
Adding a very simple locking mechanism:
create table t43 (id number);
insert into t43 values(null);
create package body p42 as
procedure busy is
x number;
begin
update t43 set id = 1;
dbms_output.put_line('Started ' || systimestamp);
...
end p42;
Then in session A:
update t42 set id = id + 1 where id = 1;
1 rows updated.
Started 08-AUG-13 18.22.35.058741000 +01:00
Finished 08-AUG-13 18.22.39.288557000 +01:00
rollback;
And simultaneously in session B:
update t42 set id = id + 1 where id = 2;
Started 08-AUG-13 18.22.40.385602000 +01:00
Finished 08-AUG-13 18.22.43.995601000 +01:00
1 row updated.
rollback;
Now the calls from the two sessions are serialised too. The session B update's procedure call can't start until session A has rolled back, so if session A did multiple actions it could block for longer.
