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
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.
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.
CURRVAL. returns the current value of a sequence. NEXTVAL. increments the sequence and returns the next value.
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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With