通过 Pragma 自治事务在触发器中使用提交/回滚

Question

我试图在某个地方解决防止表以与相同的方式通过触发器删除记录的问题。

我认为另一种方法是在特定表上发生删除后，触发器将提交该删除并将回滚以达到前一阶段，从而防止删除。

但是，似乎没有发生这种情况，因为我想提交状态与表删除状态的状态不同。有什么方法可以通过触发器提交来提交已删除的状态，然后回滚，就像我们通过插入/更新的关键字在触发器中获取更新/插入记录的状态一样下面是一个示例代码。

     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;

Answer 1

之后提交并回滚？这是行不通的。但是，像这样的触发器可能：

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>

---

[闪回]

提交是不可逆的;回滚不会影响它。但是，闪回 - 如果您的数据库版本支持它 - 可能会很有趣。看一看：

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>