oracle 增量设为3 循环,Oracle自动增量触发问题

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;

The script for trigger:

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;

The error I am getting:

[Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement

Thanks a lot.

解决方案

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;

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.