Trigger to insert sysdate after an insert in Oracle

Question

I tried using the following but apparently it's invalid SQL:

CREATE OR REPLACE TRIGGER QUESTION_DATE BEFORE INSERT ON QUESTION 
FOR EACH ROW
BEGIN
INSERT INTO QUESTION(CREATED_TIMESTAMP) 
VALUES (SYSDATE);
END;

The Question table looks like this so far:

CREATE TABLE QUESTION
(   
    QUESTION_ID             INTEGER not null,
    LATEST_QUESTION         INTEGER not null,
    CREATED_USER_ID         INTEGER not null,
    CREATED_TIMESTAMP       TIMESTAMP not null,     
    CONSTRAINT PK_QUESTION  PRIMARY KEY (QUESTION_ID)
);

CREATE SEQUENCE QUESTION_ID_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE NOORDER;

CREATE TRIGGER QUESTION_INSERT BEFORE INSERT ON QUESTION 
FOR EACH ROW
BEGIN
SELECT QUESTION_ID_SEQ.nextval
INTO :new.QUESTION_ID
FROM dual;
END;

I'm using Toad for Oracle V9.0.1.8 if that's relevant

Answer 1

Dont use a trigger to set a default value in Oracle. Instead, use "DEFAULT" on the column. Here is an exmple column

CREATED_TIMESTAMP  TIMESTAMP  DEFAULT SYSDATE  NOT NULL,

Answer 2

I think you probably want this:

CREATE OR REPLACE TRIGGER QUESTION_DATE BEFORE INSERT ON QUESTION 
FOR EACH ROW
BEGIN
 :NEW.CREATED_TIMESTAMP := SYSDATE;
END;

Your trigger tries to insert another row into QUESTION, which would fire the trigger and...

Answer 3

:new.created_timestamp := sysdate

Instead of insert.

The insert is already occurring, no need to do it again.

You could also make sysdate the default for the column, but that would allow for the value to be overridden in the insert statement.