Hi ,
I have a problem with SQLite as I try to create a trigger .
I created the SQL commands using the SQLiteman - 1.2.2 :
http://sqliteman.com/page/2.html
Which uses SQLite 3
The SQL is as follows :
[em]create table if not exists
Photo
(
id INTEGER PRIMARY KEY ASC AUTOINCREMENT
, Name VARCHAR ( 255 )
, Image BLOB
, Date_insert DATETIME
);
CREATE UNIQUE INDEX idx_photo
ON Photo (id COLLATE NOCASE ) ;
CREATE TRIGGER AFTER INSERT trg_a_insert
ON Photo
BEGIN
Photo date_insert UPDATE SET = datetime ( 'now ’ ) WHERE id = new.id ;
END;[/em]
To test the SQLiteman - [em]INSERT INTO Photo (name) VALUES ( ‘Test’ ) ;[/em]
All these commands work perfectly when run directly on SQLiteman .
However , when I run the commands to create the structure through C # using GHI.Premium.SQLite , it runs normally without showing any error .
But when I try to run even simple insert command , it hangs .
I was testing the possibilities that could cause this error and I noticed that when I do not create the trigger , everything works normally , but if the table has the trigger then the insert command hangs .
This is the source code :
private void SetupDataBase(string baseDir, string dataBaseName)
{
try
{
_dbNETMF = new Database();
_dbNETMF.Open("\\" + baseDir + "\\" + dataBaseName); // Open or Create new database file
_dbNETMF.ExecuteNonQuery(@ "drop table if exists Photo");
_dbNETMF.ExecuteNonQuery(@ "create table if not exists
Photo
(id INTEGER PRIMARY KEY ASC AUTOINCREMENT
, name VARCHAR(255)
, image BLOB
, date_insert DATETIME)");
_dbNETMF.ExecuteNonQuery(@ "CREATE UNIQUE INDEX idx_photo
ON Photo ( id COLLATE NOCASE )");
_dbNETMF.ExecuteNonQuery(@ "CREATE TRIGGER trg_a_insert AFTER INSERT
ON Photo
BEGIN
UPDATE Photo SET date_insert = datetime('now') WHERE id = new.id;
END");
// Hangs here
_dbNETMF.ExecuteNonQuery("INSERT INTO Photo (name) VALUES ('Test')");
SQLiteDataTable result = _dbNETMF.ExecuteQuery("SELECT * FROM Photo");
_dbNETMF.Close();
_dbNETMF = null;
}
catch (Exception e)
{
Debug.Print(e.Message);
Debug.Print(Database.GetLastError());
}
}
Does anyone know the reason that can cause this?
Regards,
Andrew Paes