Oracle 触发器：raise_application_error
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/582280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Trigger: raise_application_error
提问by ddejmek
I want to use the raise_application_error-procedure to stop the login process. I wrote a trigger, that checks the TERMINAL String, if it is right (I know that isn't realy secure, but at first, it is enough) So the Trigger works fine and does what i want, but the raise_application_error causes an rollback and sends not the exception that I want. Whenn I log into the DB with my Application, the raise_application_error doesnt stop the app. First question: Is this the right way, to stop logon the db with the wrong application? Second question: If yes, what is wrong?
我想使用 raise_application_error-procedure 来停止登录过程。我写了一个触发器，它检查 TERMINAL 字符串，如果它是正确的（我知道这不是很安全，但一开始就足够了）所以触发器工作正常并且做我想要的，但是 raise_application_error 导致回滚并且不发送我想要的异常。当我使用我的应用程序登录数据库时， raise_application_error 不会停止应用程序。第一个问题：这是停止使用错误应用程序登录数据库的正确方法吗？第二个问题：如果是，有什么问题？
create or replace
TRIGGER after_logon_on_database
AFTER LOGON ON DATABASE
BEGIN
IF sys_context('USERENV', 'TERMINAL')='IAS' THEN
INSERT INTO event_log
(event_date, event_time, username, event_case, event_comment)
VALUES
(SYSDATE, to_char(sysdate, 'hh24:mi:ss'), USER, 'LOGON-SUCCESS', sys_context('USERENV', 'TERMINAL'));
ELSE
INSERT INTO event_log
(event_date, event_time, username, event_case, event_comment)
VALUES
(SYSDATE, to_char(sysdate, 'hh24:mi:ss'), USER, 'LOGON-FAILURE', sys_context('USERENV', 'TERMINAL'));
RAISE_APPLICATION_ERROR(-20001, 'Access denied!');
END IF;
END after_logon_on_database;
回答by tuinstoel
Read this ask tom-thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3236035522926
阅读这个询问 tom-thread：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 3236035522926
回答by Thomas Jones-Low
In the second part of the IF/ELSE add a commit; statement between the Insert and the Raise. This will ensure that the Login failure message is inserted into the database correctly.
在 IF/ELSE 的第二部分添加一个提交；Insert 和 Raise 之间的语句。这将确保登录失败消息正确插入到数据库中。
You are aware the the on-logon trigger won't stop the user from logging in if they are a DBA (have the DAB role). This is a feature to ensure that someone can always get access to the database to fix a broken on-logon trigger.
您知道如果用户是 DBA（具有 DAB 角色），登录时触发器不会阻止用户登录。这是一项功能，可确保某人始终可以访问数据库以修复损坏的登录触发器。
You are also correct in that the trigger won't raise (as the first error message returned by Oracle) the error -20001. It will instead return a -604 (ORA-00604: error occurred at recursive SQL level 1). You are not directly executing the trigger at login, it's executed at a few steps removed. You will want your application to handle this error properly.
您也是正确的，因为触发器不会引发（作为 Oracle 返回的第一条错误消息）错误 -20001。相反，它将返回 -604（ORA-00604：递归 SQL 级别 1 发生错误）。您不是在登录时直接执行触发器，而是在删除了几个步骤后执行。您将希望您的应用程序正确处理此错误。
