sql - 为什么在触发器内部未执行 raise_application_error 之前的代码？

Question

如果我创建这个触发器，然后在表上使用 drop 或 truncate 时会引发错误，但是没有任何内容插入到 logTable 中，但是如果我删除 RAISE_APPLICATION_ERROR... 然后值被插入到 logTable 中，但是 drop/truncate 也被执行。为什么？如何避免在架构上删除/截断(如果我使用触发器而不是触发器，则仅当架构所有者正在删除/截断某些内容时才会触发它)。

CREATE OR REPLACE TRIGGER trigger_name
BEFORE DROP OR TRUNCATE ON DATABASE
DECLARE
username varchar2(100);
BEGIN
  IF ora_dict_obj_owner = 'MySchema' THEN
    select user INTO username from dual; 
    INSERT INTO logTable VALUES(username , SYSDATE);
    RAISE_APPLICATION_ERROR (-20001,'ERROR, YOU CAN NOT DELETE THIS!!');
  END IF;
END;

Answer 1

根据documentation :

Statement-Level Atomicity

Oracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails.

A successful statement is different from a committed transaction. A single SQL statement executes successfully if the database parses and runs it without error as an atomic unit, as when all rows are changed in a multirow update.

If a SQL statement causes an error during execution, then it is not successful and so all effects of the statement are rolled back. This operation is a statement-level rollback.

该过程是一个 PL/SQL 语句，它是原子的，如果您在该过程中引发错误，那么整个过程将失败并且 Oracle 将回滚该过程所做的所有更改。

---

但是您可以使用 AUTONOMOUS_TRANSACTION Pragma 创建一个过程为了绕过这种行为，以这种方式:

CREATE TABLE logtable(
   username varchar2(200),
   log_date date
);

CREATE OR REPLACE PROCEDURE log_message( username varchar2 ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO logtable( username, log_date ) VALUES ( username, sysdate );
  COMMIT;
END;
/


CREATE OR REPLACE TRIGGER trigger_name
BEFORE DROP OR TRUNCATE ON DATABASE
DECLARE
username varchar2(100);
BEGIN
  IF ora_dict_obj_owner = 'TEST' THEN
    log_message( user );
    RAISE_APPLICATION_ERROR (-20001,'ERROR, YOU CAN NOT DELETE THIS!!');
  END IF;
END;

---

现在:

drop table table1;

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: ERROR, YOU CAN NOT DELETE THIS!!
ORA-06512: at line 6
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

select * from logtable;

USERNAME  LOG_DATE
--------  -------------------
TEST      2018-04-27 00:16:34