Oracle自动增量触发问题 [英] Oracle auto increment trigger issue

问题描述

在Oracle 11g中创建自动增量触发器时遇到问题.如果有人能指出我做错了什么，我将不胜感激.我的序列脚本是这样的:

I am having an issue when creating the auto increment trigger in Oracle 11g. If someone can point out what I am doing wrong, I would really appreciate it. My script for the sequence is this :

CREATE SEQUENCE SPSS_QUOTE_LINE_ITEMS_SEQ start with 1
increment by 1
minvalue 1;

用于触发的脚本:

CREATE OR REPLACE TRIGGER SPSSQUOTELINEITEMS_ON_INSERT
BEFORE INSERT ON SPSS_QUOTE_LINE_ITEMS
FOR EACH ROW

BEGIN
 SELECT SPSS_QUOTE_LINE_ITEMS_SEQ.NEXTVAL
 INTO   :new.line_num
 FROM   dual;
END;

我得到的错误: [代码:900，SQL状态:42000] ORA-00900:无效的SQL语句

The error I am getting: [Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement

非常感谢.

推荐答案

执行此操作以修改触发器的正确​​方法如下:

The correct way of doing this to modify your trigger as below:

CREATE OR REPLACE TRIGGER SPSSQUOTELINEITEMS_ON_INSERT
BEFORE INSERT ON SPSS_QUOTE_LINE_ITEMS
FOR EACH ROW
BEGIN

 :new.line_num := SPSS_QUOTE_LINE_ITEMS_SEQ.NEXTVAL;
 --No need to write any Select Into statement here.
END;

或者，如果我按照您的方式行事，那么它就会变得

Or if i follow your way then it goes like

CREATE OR REPLACE TRIGGER SPSSQUOTELINEITEMS_ON_INSERT
BEFORE INSERT ON SPSS_QUOTE_LINE_ITEMS
FOR EACH ROW
declare
var number;

BEGIN

SELECT SPSS_QUOTE_LINE_ITEMS_SEQ.NEXTVAL
 INTO   var
 FROM   dual;

 :new.line_num :=var;

END;

通常在触发器中使用这些术语::old引用列的旧值，而:new引用新值.

You normally use the terms in a trigger using :old to reference the old value of the column and :new to reference the new value.

这篇关于Oracle自动增量触发问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！