In earlier versions of Oracle documentation, there was a note about the differences of BEFORE EACH ROW and AFTER EACH ROW triggers (Other than the obvious, AFTER EACH ROW triggers fire AFTER the DML has executed on the row and you can't update the :new pseudorecord). The note from the 10g documentation stated: "Unlike BEFORE row triggers, AFTER row triggers lock rows." (
https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm#i6052 )
However, I can't find the same note in the 12cR2 or 19c documentation. So, I have two questions:
-Is this still applicable in 12cR2/19c?
-Is the lock that is mentioned an extension (or delay to release) the lock which was obtained during the DML? Or, is it a separate lock obtained by the trigger?
I think that is gone for a reason...I'm pretty sure it was never the case. A row is locked during the entire statement, not just for "some" of the triggers. We can test that easily enough:
SQL> create table t (x int );
Table created.
SQL> insert into t values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> create or replace trigger TRG1
2 before update on t
3 for each row
4 begin
5 dbms_lock.sleep(30);
6 end;
7 /
Trigger created.
Now if the 'before update' did not take a lock, then I would have 30 seconds in which to grab that row whilst the before-trigger was running. But if I do this:
Session 1: SQL> update t set x = 10;
Session 2: SQL> select * from t for update;
the second one is blocked immediately...