CODEWITHSUNDEEP
sqloracle-databasetriggers
Himanshu
Himanshu

Reputation: 3970

Usage of Commit/ Rollback in Triggers via Pragma Autonomous Transaction

I was trying somewhere to solve a problem of preventing a table from getting records deleted via a trigger in a manner opposite to the same.

An alternate way I thought was after the delete happens on a particular table, the trigger will commit that delete and will get rollback to reach the previous stage thus, preventing deletion.

But, It seems like thats not happening as I guess the state of commit is not the sameas that of tables deleted state. Is there any way i could get that deleted state committed via triggers commit and then rollback like we do get states of updated/inserted records in the trigger via keywords inserted/updated Below is a sample code.

     CREATE OR REPLACE  TRIGGER 
     GET_DEL_INSTANCE
    AFTER DELETE ON Demo_Table
      DECLARE
      PRAGMA 
      AUTONOMOUS_TRANSACTION;
        BEGIN

       Raise_Aplication_Error() // just to have usage of commit and rollback in trigger
       COMMIT;
       ROLLBACK;
         END;

Upvotes: 0

Views: 844

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

Commit and rollback afterwards? That's not going to work. But, a trigger like this might:

SQL> create table test (id number);

Table created.

SQL> insert into test (id) select level from dual connect by level < 4;

3 rows created.

SQL> create or replace trigger trg_bd_test
  2    before delete on test
  3    for each row
  4  begin
  5    raise_application_error(-20000, 'Delete is not allowed');
  6  end;
  7  /

Trigger created.

SQL> select * From test;

        ID
----------
         1
         2
         3

SQL> delete from test where id = 2;
delete from test where id = 2
            *
ERROR at line 1:
ORA-20000: Delete is not allowed
ORA-06512: at "SCOTT.TRG_BD_TEST", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG_BD_TEST'


SQL>

[Flashback]

Commit is irreversible; rollback won't affect it. However, flashback - if your database version supports it - might be interesting. Have a look:

SQL> alter trigger trg_bd_test disable;

Trigger altered.

SQL> delete from test where id = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * From test;

        ID
----------
         1
         3

SQL> rollback;

Rollback complete.

SQL> select * from test;

        ID
----------
         1
         3

SQL> select * From test as of timestamp to_timestamp('21.07.2019 22:10', 'dd.mm.yyyy hh24:Mi');

        ID
----------
         1
         2
         3

SQL>

Upvotes: 1

Related Questions