Geek Answers Handbook

Sqlite UPDATE fires trigger with INSERT statements?

I am working on creating a simple SQLite database for access through Python. So far I have one base table and several triggers - I want one trigger to update the "date_added" field column when adding a new record, and the other to update the "date_updated" column when the record is later updated. Here is my SQLite syntax for 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 work fine ... it fires when I add a new record through the sql INSERT command, as planned.

The problem is the "update_contact" trigger ... it fires both when the record is updated using the sql UPDATE command (as planned), and when a new record is added:

. , "date_added" "date_updated":

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

, :

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

, , UPDATE INSERT?

,

: , ?

+5
2

INSERT. , INSERT . .

+6

DEFAULT ( DATETIME('NOW') ) date_added , INSERT.

+8

More articles:


All Articles