SQL 创建在插入时引发异常的触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20892659/
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
Creating trigger which throws an exception on insert
提问by devBem
Hello fellow programmers and happy new year to you all!
各位程序员大家好，祝大家新年快乐！
I have few university tasks for winter break and one of them is to create trigger on table:
我寒假的大学任务很少，其中之一是在桌子上创建触发器：
PERSON(ID, Name, Surname, Age);
Trigger is supposed to inform user when they have inserted row with invalid ID. Vadility criteria is that ID is 11 digits long.
触发器应该在用户插入具有无效 ID 的行时通知用户。Vadility 标准是 ID 的长度为 11 位。
I tried to write solution like this:
我试着写这样的解决方案：
CREATE OR REPLACE TRIGGER person_id_trigg
AFTER INSERT
ON person
DECLARE
idNew VARCHAR(50);
lengthException EXCEPTION;
BEGIN
SELECT id INTO idNew FROM INSERTED;
IF LENGTH(idNew) <> 11 THEN
RAISE lengthException;
END IF;
EXCEPTION
WHEN lengthException THEN
dbms_output.put_line('ID for new person is INVALID. It must be 11 digits long!');
END;
Then I realized that INSERTED exists only in sqlserver and not in oracle.
然后我意识到 INSERTED 只存在于 sqlserver 中，而不存在于 oracle 中。
What would you suggest I could do to fix that?
你建议我能做些什么来解决这个问题？
Thanks in advance!
提前致谢！
回答by Justin Cave
Do you want to raise an exception (which would prevent the insert from succeeding)? Or do you want to allow the insert to succeed and write a string to the dbms_outputbuffer that may or may not exist and may or may not be shown to a human running the insert?
是否要引发异常（这会阻止插入成功）？或者您是否希望允许插入成功并将一个字符串写入dbms_output缓冲区，该字符串可能存在也可能不存在，并且可能会或可能不会向运行插入的人显示？
In either case, you'll want this to be a row-level trigger, not a statement-level trigger, so you'll need to add the for each rowclause.
无论哪种情况，您都希望这是一个行级触发器，而不是语句级触发器，因此您需要添加for each row子句。
CREATE OR REPLACE TRIGGER person_id_trigg
AFTER INSERT
ON person
FOR EACH ROW
If you want to raise an exception
如果你想引发异常
BEGIN
IF( length( :new.id ) <> 11 )
THEN
RAISE_APPLICATION_ERROR( -20001,
'The new ID value must have a length of 11' );
END IF;
END;
If you want to potentially print output but allow the insertto succeed
如果您想潜在地打印输出但允许insert成功
BEGIN
IF( length( :new.id ) <> 11 )
THEN
dbms_output.put_line( 'The new ID value must have a length of 11' );
END IF;
END;
Of course, in reality, you would never use a trigger for this sort of thing. In the real world, you would use a constraint.
当然，在现实中，你永远不会为这种事情使用触发器。在现实世界中，您将使用约束。
