SQL> show user
USER is "ANDREY"
SQL> col tcol for a10
SQL> drop table testtab;

Table dropped.

SQL> drop table testtab_del;

Table dropped.

SQL>
SQL> create table testtab (tcol varchar2(100));

Table created.

SQL> create table testtab_del (tcol varchar2(100));

Table created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER test_trg
  2    BEFORE DELETE ON testtab
  3    FOR EACH ROW
  4      BEGIN
  5       insert into testtab_del values(:old.tcol)
  6      end;
  7      /

Trigger created.

SQL>
SQL> select * from testtab;

no rows selected

SQL> insert into testtab values('val1');

1 row created.

SQL> delete from testtab where tcol='val1';

1 row deleted.

SQL> select * from testtab;

no rows selected

SQL>
SQL> insert into testtab values('val2');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from testtab;

TCOL
----------
val2

SQL> select * from testtab_del;

TCOL
----------
val1

SQL>
SQL>

SQL> commit;

Commit complete.

SQL> create table testtab (tcol varchar2(100));

Table created.

SQL> create table testtab_del (tcol varchar2(100));

Table created.

SQL> create or replace trigger test_trg
  2    before delete on testtab
  3    for each row
  4  begin
  5    insert into testtab_del values (:old.tcol);
  6  end;
  7  /

Trigger created.

SQL> insert into testtab values ('val1');

1 row created.

SQL> -- it is irrelevant whether you COMMIT here or not. ROLLBACK would matter, though,
SQL> -- because there wouldn't be any row in the TESTTAB table.
SQL> -- Let's try ROLLBACK:
SQL> rollback;

Rollback complete.

SQL> delete from testtab where tcol = 'val1';

0 rows deleted.

SQL> select * from testtab_del;

no rows selected

SQL> -- of course, there's nothing there as we deleted 0 records
SQL>
SQL> insert into testtab values ('val1');

1 row created.

SQL> -- Let's try COMMIT:
SQL> commit;

Commit complete.

SQL> delete from testtab where tcol = 'val1';

1 row deleted.

SQL> select * From testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- the same result as you got (and you didn't commit). If you commit now,
SQL> -- DELETE will become permanent and
SQL> -- a) there will be no records in TESTTAB table
SQL> -- b) there will be 1 record in TESTTAB_DEL table
SQL> -- So let's try it:
SQL> commit;

Commit complete.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- Moreover, it will be permanent "forever" (even if I disconnect):
SQL> disco
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> connect scott/tiger@ora10
Connected.

Session altered.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- See? Permanent. Let's see what happens with ROLLBACK.
SQL> delete from testtab_del;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into testtab values ('val1');

1 row created.

SQL> delete from testtab;

1 row deleted.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- Rollback:
SQL> rollback;

Rollback complete.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

no rows selected

SQL> -- No record in any of these tables. Why? Because ROLLBACK reverted the INSERT
SQL> -- statement as well, not only the DELETE.
SQL> -- Once again, but this time commit the INSERT.
SQL> insert into testtab values ('val1');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from testtab;

1 row deleted.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- Rollback - at this point - will rollback only the DELETE; INSERT isn't affected
SQL> -- because it was already committed.
SQL> rollback;

Rollback complete.

SQL> select * from testtab;

TCOL
--------------------------------------------------------------------------------
val1

SQL> select * from testtab_del;

no rows selected

SQL>

SQL> drop table testtab;

Table dropped.

SQL> drop table testtab_del;

Table dropped.

SQL> create table testtab (tcol varchar2(100));

Table created.

SQL> create table testtab_del (tcol varchar2(100));

Table created.

SQL> create or replace trigger test_trg
  2    before delete on testtab
  3    for each row
  4  declare
  5    pragma autonomous_transaction;
  6  begin
  7    insert into testtab_del values (:old.tcol);
  8    commit;
  9  end;
 10  /

Trigger created.

SQL> -- Note the COMMIT statement within the trigger; normally, you can't commit in a
SQL> -- trigger, but autonomous transaction allows you to do that.
SQL> insert into testtab values ('val1');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from testtab;

1 row deleted.

SQL> -- Obviously, there's no record in TESTTAB, but it is inserted into the TESTTAB_DEL:
SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- ROLLBACK now. Previously, without the autonomous transaction, the result was:
SQL> -- record was put back into the TESTTAB and removed from TESTTAB_DEL.
SQL> -- With the autonomous transaction, record is put back into the TESTTAB, but - because
SQL> -- of COMMIT in a trigger - the record remains in TESTTAB_DEL.
SQL> rollback;

Rollback complete.

SQL> select * from testtab;

TCOL
--------------------------------------------------------------------------------
val1

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL>