SQL / SQLite trigger question

[x1hundredregrets]

Hey all,

I'm writing a server app and client app in C# to handle some file caching.

I have these two tables:

mycommand.CommandText = "CREATE TABLE IF NOT EXISTS 'cache' (id INTEGER PRIMARY KEY, md5 VARCHAR(32), filename char(255), timeAdded TIMESTAMP, timeLastRef TIMESTAMP, fileize INTEGER, filedata BLOB)";
mycommand.ExecuteNonQuery();
mycommand.CommandText = "CREATE TABLE IF NOT EXISTS 'info' (totalFiles INTEGER, totalFileSize INTEGER)";
mycommand.ExecuteNonQuery();



I want to update the 'info' table's totalFiles and totalFileSize whenever a DELETE or INSERT is done on 'cache'. I need to use triggers since they are an atomic operation as far as SQLite is concerned.

Does anyone know the correct syntax for something like that? I googled it and came up with very very little info that applies to my specific case.

Thanks in advance!

[rangoth]

CREATE TRIGGER *trigger name*
ON *table name*
FOR INSERT
AS
*action to commit*

[x1hundredregrets]

I got that much done, lol. I'm wondering how I access the filesize column from the row being INSERTED or DELETED? Heres what I have so far, but it's throwing an exception because the syntax is wrong somehow:

mycommand.CommandText = "CREATE TRIGGER IF NOT EXISTS 'updateInfoOnDelete' DELETE ON 'cache' BEGIN UPDATE 'info' SET 'totalFiles'=totalFiles-1; UPDATE 'info' SET 'totalFileSize'=totalFileSize-old.filesize; END";
mycommand.ExecuteNonQuery();
mycommand.CommandText = "CREATE TRIGGER IF NOT EXISTS 'updateInfoOnInsert' INSERT ON 'cache' BEGIN UPDATE 'info' SET 'totalFiles'=totalFiles+1; UPDATE 'info' SET 'totalFileSize'=totalFileSize+old.filesize; END";
mycommand.ExecuteNonQuery();

Thanks again for the quick reply.