Creating Oracle Sequence Trigger

Question

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

ID needs to be the actual field in the ACCOUNTS table, too. — vapcguy
– vapcguy, Commented Sep 21, 2016 at 15:44

Jon Heller · Accepted Answer · 2019-04-01 02:56:17Z

24

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;

edited Apr 1, 2019 at 2:56

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

answered Sep 4, 2014 at 8:37

Darshan Lila

5,8762 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user1928388 Over a year ago

Unfortunately, trigger was created bu I can't compile it. Error(8,11): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ;

user1928388 Over a year ago

Now trigger sql looks like: 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

Darshan Lila Over a year ago

Make sure you are using / after the block.

user1928388 Over a year ago

I'm sure but same result

Darshan Lila Over a year ago

and also after from dual you need to add END;

|

user6159630 · Accepted Answer · 2016-04-05 06:33:25Z

2

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;

answered Apr 5, 2016 at 6:33

user6159630

311 bronze badge

Collectives™ on Stack Overflow

Creating Oracle Sequence Trigger

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related