When I did the following (create table, create seq, create trigger, and try to insert a row), I got the insert error(SQL Error: ORA-00928: missing SELECT keyword).
In https://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert.html
it says,
++++++++++++++
If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value:
INSERT INTO tbl_name () VALUES();
In strict mode, an error occurs if any column doesn't have a default value. Otherwise, MySQL uses the implicit default value for any column that does not have an explicitly defined default.
+++++++++++++++
But, then why am I getting an error when trying to insert using the default for the i_date? I though after i_date gets the insert value, the id will do the auto-increment. How do I make this work? I am using 11g2.
I know that if I have another column in test1 like 'desc' and insert a row with the 'desc' value without having the id and i_date , it inserts fine and takes the appropriate values for the id and i_date.
Thank you very much.
====================================================
CREATE TABLE test1
(id NUMBER PRIMARY KEY,
i_date date default sysdate);
CREATE SEQUENCE test1_sequence
START WITH 1
INCREMENT BY 1;
CREATE OR REPLACE TRIGGER test1_trigger
BEFORE INSERT
ON test1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test1_sequence.nextval INTO :NEW.ID FROM dual;
END;
/
INSERT INTO test1 () VALUES ();
SELECT * FROM test1;
======================================================
Table TEST1 created.
Sequence TEST1_SEQUENCE created.
Trigger TEST1_TRIGGER compiled
Error starting at line : 30 in command -
INSERT INTO test1 () VALUES ()
Error at Command Line : 30 Column : 20
Error report -
SQL Error: ORA-00928: missing SELECT keyword
00928. 00000 - "missing SELECT keyword"
*Cause:
*Action:
no rows selected