CODEWITHSUNDEEP
triggersinsertsqlite
RoboTamer
RoboTamer

Reputation: 3524

CREATE TRIGGER x BEFORE INSERT ON y IF q = ''

Following is a sample. I am trying to use BEFORE INSERT ON SET and have been to pages of google to look for help. This is the closest I can get:

CREATE TABLE user (alias TEXT, added TEXT);

CREATE TRIGGER user_insert_time 
  BEFORE INSERT ON user 
    FOR EACH ROW 
    BEGIN SET new.added = (datetime('NOW','UTC')));
  END;

OR

CREATE TRIGGER user_insert_time 
  BEFORE INSERT ON user 
    FOR EACH ROW BEGIN 
    IF (new.added = '') 
    THEN SET new.added = (datetime('NOW','UTC')));
  END;

And I know, I could do following, but then I have to explicitly call out the fields, besides I am kinda hung up on this. 

CREATE TABLE user (alias TEXT, added TEXT DEFAULT (datetime('NOW','UTC')));

Upvotes: 0

Views: 2804

Answers (1)

Stefanos Michael Sofroniou
Stefanos Michael Sofroniou

Reputation: 21

Well, in SQLite3 I do this to sort this similar issue:

CREATE TRIGGER [trigger_name]
AFTER INSERT
ON my_table
BEGIN UPDATE
      my_table SET my_date = datetime('now','localtime')      
      WHERE ROWID = NEW.ROWID;
END

Upvotes: 2

Related Questions