How to Update Fields On Insert using Trigger

Question

I've created and worked with Triggers in Oracle for years however I'm unable to wrap my head around how to update a field when inserting data into a sqlite database.

All I want to do is create a trigger that automatically inserts the current DateTime into a column in the sqlite database named 'createdDate' for ANY record that is inserted.

What is the best approach to accomplish this?

Below is what I've attempted without success.

CREATE TRIGGER outputLogDB_Created_Trig
    BEFORE INSERT
        ON outputLog
      WHEN NEW.createdDate IS NULL
BEGIN
    SELECT CASE WHEN NEW.createdDate IS NULL THEN NEW.createdDate = datetime('now', 'localtime') END;
END;

The above is almost a replica of how I would implement my triggers in Oracle with some modifications of course for sqlite. The logic is basically identical.

What am I missing?

Later Edit - I can get it to work if I instead use AFTER INSERT and not using FOR EACH ROW

CREATE TRIGGER outputLog_Created_Trig
     AFTER INSERT
        ON outputLog
      WHEN New.createdDate IS NULL
BEGIN
    UPDATE outputLog
       SET createdDate = datetime('now', 'localtime') 
     WHERE outputLog_ID = New.rowid;
END;

But why can't I just insert the record using the new value while I'm inserting it? Am I ONLY able to get this in there using an Update AFTER I've already inserted the record?

The issue I have with this is the fact that I'd like to have a NOT NULL constraint on the createdDate column. Perhaps I'm simply used to how I've done it for years in Oracle? I realize the Trigger 'should' take care of any record and force this field to NEVER be NULL. It's just that NOT being able to add the constraint for some reason makes me uneasy. Do I need to let go of this worry?

Answer 1

Thanks to Shawn pointing me toward an easy simple solution to my problem. All that is needed in a SQLite database to insert the current Date/Time for each record being inserted is to set the DEFAULT value on that column to CURRENT_TIMESTAMP.

Since I wanted the timestamp in my own local time see below my create table script that is the solution to my problem.

CREATE TABLE outputLog (
    outputLog_ID INTEGER  PRIMARY KEY ASC ON CONFLICT ROLLBACK AUTOINCREMENT
                          NOT NULL ON CONFLICT ROLLBACK,
    outputLog    TEXT,
    created      DATETIME DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime') ) 
                          NOT NULL )
;