如何使用触发器获取序列中的下一个值
我刚刚学习Oracle，我了解触发器和序列的概念。我似乎找不到的是它们如何以类似于 SqlServer Identity 设置的自动增量方式一起使用。在一个非常简单的示例中，我有一个名为“Employees”的表，其中包含三个字段（均为必需字段）：EmployeeID (PK)、FirstName、LastName。我创建了一个序列来获取 ID 字段的下一个值。然后我创建了一个如下所示的触发器：
CREATE OR REPLACE TRIGGER MyFirstTrigger
BEFORE INSERT ON EMPLOYEES FOR EACH ROW
BEGIN
Select EMP_SEQ.NextVal into :NEW.EMPLOYEEID from dual;
END MyFirstTrigger;
/
但是，它如何在插入语句中使用？仅包含 FirstName 和 LastName 值的插入语句因“值不足”而失败（顺便说一句，我正在使用 Toad）。如果我必须包含 EMP_SEQ.NextVal 作为插入语句的第一个值，那么触发器的意义何在？
希望这是有道理的。提前致谢。
I'm just learning Oracle and I understand the concept of triggers and sequences. What I can't seem to find is how they are used together in an auto-increment fashion similar to a SqlServer Identity setting. In a really simple example, I have a table called Employees that has three fields (all required): EmployeeID (PK), FirstName, LastName. I have created a sequence to get the next value for the ID field. I then created a trigger that looks like so:
CREATE OR REPLACE TRIGGER MyFirstTrigger
BEFORE INSERT ON EMPLOYEES FOR EACH ROW
BEGIN
Select EMP_SEQ.NextVal into :NEW.EMPLOYEEID from dual;
END MyFirstTrigger;
/
However, how is this used in an insert statement? An insert statement with only the FirstName and LastName values fails for "Not enough values" (I'm using Toad, by the way). If I have to include EMP_SEQ.NextVal as the first value of the insert statement, what's the point of the trigger?
Hopefully, this makes sense. Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以省略插入中的 ID，但必须指定目标列：
在生产代码中不应该有没有列列表的插入；但是如果你这样做了，你可以为 D 列传递 NULL：
You can omit the ID from the insert, but you must specify the target columns:
You should not have inserts without a column list in production code; however if you did you could just pass a NULL for the D column:
这应该有效，如果没有，我将描述其可能的原因和用法。
并且你肯定能够做到这一点
This should work if not i will describe the possible reason of that and usage.
and for sure You will be able to do this