0

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.

sstan
  • 35,425
  • 6
  • 48
  • 66
John
  • 5
  • 4
  • 2
    Possibly same issue: http://stackoverflow.com/questions/29450495/autoincrement-in-oracle-with-seq-and-trigger-invalid-sql-statement – Codo Aug 22 '16 at 16:24
  • What client are you using? – sstan Aug 22 '16 at 17:08
  • Why not simply `:new.line_num := SPSS_QUOTE_LINE_ITEMS_SEQ.NEXTVAL;` instead of `select ... from dual;` – Wernfried Domscheit Aug 22 '16 at 17:33
  • @Codo, I have tried using the suggestions but when I add / at the end of my script, I am getting another error that says [Code: 17439, SQL State: 99999] Invalid SQL type: sqlKind = UNINITIALIZED. – John Aug 22 '16 at 17:34
  • @sstan, I am using DBVisualizer – John Aug 22 '16 at 17:36
  • I don't know DBVisualizer. But most SQL tools have at least two buttons, one to run an SQL command returning a result set and one to run a SQL script, i.e. one or more SQL statements without a result set. Do you use the *script* button? Furthermore, you probably need to add a forward slash as the last one. This is required whenever there is a BEGIN or DECLARE involved. – Codo Aug 23 '16 at 06:01

1 Answers1

0

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.

XING
  • 9,608
  • 4
  • 22
  • 38