database 如何在触发器内引发异常？有没有办法做到这一点？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18682341/
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
How to raise an Exception inside a Trigger? Is there a way to do this?
提问by Saturnian
There's a situation like: If the Salary column in updated with a value lesser than it's original value, print an error message and let the update NOT happen. This is what I've written so far:
有一种情况，例如：如果 Salary 列的更新值小于其原始值，则打印一条错误消息并让更新不会发生。这是我到目前为止所写的：
CREATE OR REPLACE TRIGGER TRIG1
BEFORE UPDATE OF SAL ON EMP
for each row
USER_XCEP EXCEPTION
WHEN (NEW.SAL<OLD.SAL)
BEGIN
RAISE USER_XCEP
EXCEPTION
WHEN USER_XCEP THEN
DBMS_OUTPUT.PUT_LINE('UPDATION NOT ALLOWED - ILLEGAL VALUES');
END;
And I get the error - Incorrect Trigger Specification
我得到错误 - 不正确的触发器规范
Is there any other way to achieve this?
有没有其他方法可以实现这一目标？
回答by Ben
You're almost there; you need a DECLARE block in a triggerif you want to declare anything; this means that your WHEN clause is in the wrong place.
您快到了; 你需要一个触发一个DECLARE块，如果你要申报的东西; 这意味着您的 WHEN 子句在错误的地方。
create or replace trigger trig1
before update
of sal
on emp
for each row
when (new.sal < old.sal)
declare
user_xcep EXCEPTION;
PRAGMA EXCEPTION_INIT( user_xcep, -20001 );
begin
raise user_xcep;
end;
A few points:
几点：
- Nevercatch an exception and then call DBMS_OUTPUT.PUT_LINE; it's pointless. Someone has to be there to view the result for each and every record. If you don't want something to happen raise the exception and then catch it. I've added an error code to your exception so that you can catch this outside the trigger and handle it how you wish (don't print anything to stdout).
- It's a minor point but I've added a little whitespace; not much. I couldn't initially see where the problem was with your code because you didn't have any.
- You were missing semi-colons after the exception declaration and RAISE.
- 永远不要捕获异常然后调用 DBMS_OUTPUT.PUT_LINE；没有用。必须有人在那里查看每条记录的结果。如果您不希望发生某些事情，请引发异常然后捕获它。我在您的异常中添加了一个错误代码，以便您可以在触发器之外捕获它并按照您的意愿处理它（不要将任何内容打印到标准输出）。
- 这是一个小问题，但我添加了一点空格；不多。我最初无法看到您的代码问题出在哪里，因为您没有任何代码。
- 您在异常声明和 RAISE 之后缺少分号。
Read more about internally defined exceptions in the documentation
在文档中阅读有关内部定义异常的更多信息
