sqlite UPDATE trigger firing on INSERT statements also?

Question

I'm working on setting up a simple SQLite database to access via Python. So far I have one basic table, and a couple of triggers - I want to have one trigger update a field column 'date_added' when a new record is added, and another one to update a column 'date_updated' when a record is later updated. Here is my SQLite syntax for the triggers:

CREATE TRIGGER add_contact AFTER INSERT ON contact_info  
BEGIN  
    UPDATE contact_info SET date_added = DATETIME('NOW') WHERE pkid = new.pkid;  
END;  

CREATE TRIGGER update_contact AFTER UPDATE ON contact_info  
BEGIN  
    UPDATE contact_info SET date_updated = DATETIME('NOW') WHERE pkid = new.pkid;  
END;  

The 'add_contact' trigger seems to be working fine... it fires when I add a new record via an sql INSERT command, as planned.

The problem seems to be the 'update_contact' trigger... it fires both when I update a record via an sql UPDATE command (as planned) and when I add a new record also:

i.e. when I add a new record I get this in the 'date_added' and 'date_updated' columns:

2010-07-12 05:00:06|2010-07-12 05:00:06

and when I update that record, it changes like so:

2010-07-12 05:00:06|2010-07-12 05:14:26

I guess I'm not getting why the UPDATE trigger fires on INSERT also?

TIA,

Monte

Edited to add: Any hints on how to make it work as intended?

Answer 1

A better way to avoid the original problem is to use a DEFAULT ( DATETIME('NOW') ) clause for the date_added column in the table definition, instead of using an INSERT trigger.

Answer 2

You have an UPDATE in your INSERT trigger. So the INSERT causes an UPDATE. Which you have hooked with a different trigger.