Oracle：自动增量触发器。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16388576/
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: auto-increment trigger.
提问by Stumbler
Found the following trigger in a database which was blocking inserts on a unique varchar primary key
在阻止插入唯一 varchar 主键的数据库中找到以下触发器
CREATE OR REPLACE TRIGGER "BI_PRIVILEGE"
before insert on "PRIVILEGE"
for each row
begin
if :NEW."PRIVILEGE-ID" is null then
select "PRIVILEGE_SEQ".nextval into :NEW."PRIVILEGE-ID" from dual;
end if;
end;
Is this an auto-number generator? I can easily disable it to fix my problem, but will there be unforseen negative ramifcations for the primary key?
这是自动编号生成器吗？我可以轻松地禁用它来解决我的问题，但是主键会不会有不可预见的负面影响？
I have actually been looking for code to set up auto-increment triggers for primary keys and could use this as a template if this is what it is doing. If it is, it is likely doing it incorrectly as the primary key is specifically PRIVILEGE_IDnot PRIVILEGE-ID, also, shouldn't some sort of application_errorarise in the case of conflicts, etc?
我实际上一直在寻找代码来为主键设置自动增量触发器，如果这是它正在做的事情，可以将其用作模板。如果是，它很可能做错了，因为主键特别是PRIVILEGE_IDnot PRIVILEGE-ID，而且，application_error在发生冲突等情况下不应该出现某种情况吗？
回答by Ben
Okay, I think I get what's going on. The answer to your question is an absolutely massive yes. There can be a large impact if you disable this trigger.
好吧，我想我明白发生了什么。你的问题的答案绝对是肯定的。如果禁用此触发器，可能会产生很大影响。
The reason this trigger seems to exist is to deal with the situation where a primary key value is notprovided on an insert into your table. If this occurs anywherein your code them removing the trigger will break those inserts.
此触发器似乎存在的原因是为了处理在插入表时未提供主键值的情况。如果这发生在您的代码中的任何地方，他们删除触发器将破坏这些插入。
You have to do two things.
你必须做两件事。
Correct the trigger, it's obviously broken; fix it:
CREATE OR REPLACE TRIGGER BI_PRIVILEGE before insert on PRIVILEGE for each row begin if :NEW.PRIVILEGE_ID is null then select PRIVILEGE_SEQ.nextval into :NEW.PRIVILEGE_ID from dual; end if; end;If you're using Oracle 11G or greater then you can use this instead:
if :NEW.PRIVILEGE_ID is null then :NEW.PRIVILEGE_ID := PRIVILEGE_SEQ.nextval; end if;Work out whether this actually happens. If you do insert records without a primary key you need to find out why this happening and whether the behaviour is correct. If it is you're stuck with the trigger, otherwise fix that. If you never insert records without a primary key then you can disable the trigger.
The quickest way of finding out may be to disable the trigger anyway but it would break your inserts. If this is a production database only you can tell whether it's worth it. I wouldn't personally.
更正扳机，明显坏了；修理它：
CREATE OR REPLACE TRIGGER BI_PRIVILEGE before insert on PRIVILEGE for each row begin if :NEW.PRIVILEGE_ID is null then select PRIVILEGE_SEQ.nextval into :NEW.PRIVILEGE_ID from dual; end if; end;如果您使用的是 Oracle 11G 或更高版本，则可以改用它：
if :NEW.PRIVILEGE_ID is null then :NEW.PRIVILEGE_ID := PRIVILEGE_SEQ.nextval; end if;弄清楚这是否真的发生了。如果您在没有主键的情况下插入记录，您需要找出发生这种情况的原因以及行为是否正确。如果是，您被触发器卡住了，否则请修复它。如果您从不插入没有主键的记录，那么您可以禁用触发器。
找出问题的最快方法可能是无论如何禁用触发器，但它会破坏您的插入物。如果这是一个生产数据库，只有你才能判断它是否值得。我个人不会。
回答by Kevin Bowersox
This script will get a value from a sequence and place it into a newly inserted row in the table. It is acting very similar to an autonumber. I assume that application code relies upon this trigger to populate the primary key for inserted rows, I would not recommend removing it before evaluating an application's source code.
此脚本将从序列中获取一个值并将其放入表中新插入的行中。它的行为与自动编号非常相似。我假设应用程序代码依赖这个触发器来填充插入行的主键，我不建议在评估应用程序的源代码之前删除它。
Unless this was some forgotten about experiment, it is likely the application code depends on this trigger/sequence.
除非有人忘记了实验，否则应用程序代码很可能取决于此触发器/序列。
This is an acceptable solution for autoincrement functionality. See: How to create id with AUTO_INCREMENT on Oracle?
这是自动增量功能可接受的解决方案。请参阅：如何在 Oracle 上使用 AUTO_INCREMENT 创建 id？
回答by APC
" in this case the primary key is meant for unique strings. I cant imagine that auto-increment could be altogether useful in that context. "
“在这种情况下，主键用于唯一字符串。我无法想象自动增量在这种情况下可能完全有用。”
If you mean that "PRIVILEGE-ID" is a varchar2 column then you're sort of correct. On the one hand, a number can also be a string, so it would work as a key. But if the key is supposed to have a specific format of letters and numbers, a monotonically incrementing number won't fit the patten.
如果您的意思是“PRIVILEGE-ID”是 varchar2 列，那么您是正确的。一方面，数字也可以是字符串，因此它可以用作键。但是，如果密钥应该具有特定的字母和数字格式，则单调递增的数字将不适合该模式。
For me, the concern would be that IF statement. It suggests that sometimes the key is populated by the application and at other times it is defaulted by the database. That is messy. Apart from anything else, having two sources of keys means that the sequence is no longer guaranteed to be unique. You now have the possibility that the sequence generated nextvalwill collide with a previous manually assigned number.
对我来说，担心的是 IF 语句。它表明有时密钥由应用程序填充，有时由数据库默认。那很乱。除此之外，拥有两个密钥来源意味着不再保证序列是唯一的。您现在有可能生成的序列nextval与之前手动分配的编号发生冲突。
What to do?
该怎么办？
If you have a development environment with good coverage from automated unit or integration tests, the answer is simple: disable the trigger, run your test suites and see what fails. If that doesn't describe your set-up (and I have the feeling it doesn't) then disabling the that trigger is riskier, because you can't have confidence that you have tested all the paths which might populate the table.
如果您的开发环境具有良好的自动化单元测试或集成测试覆盖率，那么答案很简单：禁用触发器，运行您的测试套件并查看失败的内容。如果这不能描述您的设置（我感觉它没有），那么禁用该触发器的风险更大，因为您无法确信您已经测试了可能填充表的所有路径。
