如果在 Oracle SQL 中插入 null，则设置一个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28112640/
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
Set a value if null inserted in Oracle SQL
提问by user4359659
I have created a trigger, that will automatically set the first column values as subsequent factorial numbers. However, additionally, I would like to set the second column's value as the value of first incremented by 5, in case a null value is inserted. Here's what I try right now:
我创建了一个触发器，它将自动将第一列值设置为后续阶乘数。但是，另外，我想将第二列的值设置为第一个增加 5 的值，以防插入空值。这是我现在尝试的方法：
create or replace trigger test_tr
before insert on myT
for each row
begin
IF :new.mNumb is null
THEN
UPDATE myT
SET mNumb = :new.tab_id + 5;
END IF;
SELECT fac(test_seq.NEXTVAL)
INTO :new.tab_id
FROM dual;
end;
But clearly I'm missing something, as nothing happens, the inserted null is still empty.
但显然我错过了一些东西，因为没有任何反应，插入的空值仍然是空的。
回答by Emmanuel
Do not re-update the table in your trigger, update the row you're given directly:
不要重新更新触发器中的表，直接更新您给出的行：
...
IF :new.mNumb is null
THEN
:new.mNumb = :new.tab_id + 5;
END IF;
...
回答by Boneist
It all works as expected, using Emmanuel's suggestion to remove the update stmt, as far as I can tell. Here's the test case I used:
据我所知，一切都按预期工作，使用 Emmanuel 的建议删除更新 stmt。这是我使用的测试用例：
drop table test;
create table test (col1 number, col2 number);
create trigger test_trg
before insert on test
for each row
begin
IF :new.col2 is null
THEN
:new.col2 := :new.col1 + 5;
END IF;
:new.col1 := dbms_random.value;
end;
/
insert into test values (1, 1);
insert into test values (1, null);
insert into test values (null, null);
commit;
select * from test;
which produces the following output:
产生以下输出：
COL1 COL2
---------- ----------
.617580128 1
.030570358 6
.555066268
Maybe if you set :new.col1 before dealing with the null col2 scenario, that would work better for you? Doing that produces:
也许如果你在处理 null col2 场景之前设置 :new.col1 ，那对你来说会更好？这样做会产生：
COL1 COL2
---------- ----------
.302670917 1
.024927489 5.02492749
.667568400 5.66756840
