Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating Oracle Sequence Trigger

I'm trying create seuqunce trigger but error occurs when I executing the trigger create sql in SQL Developer. I don't understand, actually everything looks fine. I share the details below, please help me, thanks.

My trigger sql:

CREATE OR REPLACE TRIGGER "TRIGGER1" BEFORE INSERT ON ACCOUNTS
FOR EACH ROW
 WHEN (new."ID" IS NULL) 
BEGIN
  SELECT ACCOUNTS_SEQ.NEXTVAL 
  INTO :new."ID" 
  FROM dual;
END;
/

And error description:

Error starting at line : 5 in command -
CREATE OR REPLACE TRIGGER "TRIGGER1" BEFORE INSERT ON ACCOUNTS
FOR EACH ROW
 WHEN (new."ID" IS NULL) 
BEGIN
  SELECT ACCOUNTS_SEQ.NEXTVAL 
  INTO :new."ID" 
  FROM dual
Error report -
SQL Command: trıgger "TRIGGER1"
Failed: Warning: completed with warning

Error starting at line : 12 in command -
END
Error report -
Unknown Command
like image 484
user1928388 Avatar asked Sep 04 '14 08:09

user1928388


People also ask

What is create sequence in Oracle?

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

What is the basic syntax to create a sequence in Oracle?

The syntax to create a sequence in Oracle is: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; sequence_name. The name of the sequence that you wish to create.

What is Currval and Nextval in Oracle?

CURRVAL. returns the current value of a sequence. NEXTVAL. increments the sequence and returns the next value.


2 Answers

Try this:

CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT ON ACCOUNTS
FOR EACH ROW
  WHEN (new.ID IS NULL)
BEGIN
  :new.ID := ACCOUNTS_SEQ.NEXTVAL;
END;
/

Your sequence should look like this:

CREATE SEQUENCE ACCOUNTS_SEQ 
  START WITH 1 
  INCREMENT BY 1;
like image 128
Darshan Lila Avatar answered Oct 28 '22 22:10

Darshan Lila


CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT ON ACCOUNTS
FOR EACH ROW
BEGIN
IF :new.ID IS NULL THEN 
SELECT ACCOUNTS_SEQ.NEXTVAL 
INTO :new.ID
FROM dual;
END IF;
END;
like image 24
user6159630 Avatar answered Oct 28 '22 23:10

user6159630