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屋!