Sqlite3 INSERT trigger only fire on REPLACE statement even where record already exists?

I'm trying out the Sqlite3 REPLACE (INSERT OR REPLACE) command. I like to keep a created datetime (creDT) and an update datetime (updDT). So I created a database and a trigger for INSERT (creDT & updDT) and one for UPDATE (updDT), but each REPLACE (especially the ones where the primary key already exists) ends up with the current time in both creDT and updDT. Does REPLACE DELETE and INSERT i开发者_Go百科nstead of UPDATE?

Is this the standard behavior or am I doing something wrong?

def createDbTables(self):

    self.sqlCursor.execute("""
    CREATE TABLE rfdetector (
    sn TEXT PRIMARY KEY,
    detector TEXT,
    hex TEXT,
    updDT DATE,
    creDT DATE)
    """)

    self.sqlCursor.execute("""
    CREATE TRIGGER insert_rfdetector_creDT
    AFTER INSERT ON rfdetector
    BEGIN
    UPDATE rfdetector SET creDT = DATETIME('now','localtime') WHERE rowid = new.rowid;
    UPDATE rfdetector SET updDT = DATETIME('now','localtime') WHERE rowid = new.rowid;
    END;
    """)

    self.sqlCursor.execute("""
    CREATE TRIGGER update_rfdetector_updDT
    AFTER UPDATE ON rfdetector
    BEGIN
    UPDATE rfdetector SET updDT = DATETIME('now','localtime') WHERE rowid = new.rowid;
    END;
    """)

def insertSql(self, data):

    self.sqlCursor.execute(
    'REPLACE INTO rfdetector (sn, hex, detector) VALUES (?, ?, ?)',
    (data.serialNumber, data.hex, data.detector))

---

Looks like SQLite performs a DELETE then INSERT on REPLACE:

REPLACE

When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs, the REPLACE conflict resolution algorithm always works like ABORT.

from: http://www.sqlite.org/lang_conflict.html