I am trying to create a database called Mouse with a table called Help that is fairly simple: it has 5 fields (columns)
ID; CreateGMP; ModifyLocal; winTitle; ContentHTML
I want to create a Trigger that will Update the ModifyLocal column with the current datetime when a record is modified
Here is the code that I am using to create this database
[code]Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem(“Mouse.sqlite”, FolderItem.PathModes.Native)
Catch Error As IOException
MessageBox("The database file could not be created: " + Error.Message)
db.ExecuteSQL(“CREATE TABLE IF NOT EXISTS” + DQ + “Help” + DQ + “(ID Integer Not NULL PRIMARY KEY, CreateGMT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ModifyLocal DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP,‘localtime’)), WinTitle TEXT, ContentHTML Text NOT NULL DEFAULT ‘NewContent’)”)
db.ExecuteSQL("?CREATE TRIGGER when_change AFTER UPDATE ON Help BEGIN UPDATE Help SET ModifyLocal = datetime(CURRENT_TIMESTAMP,‘localtime’) WHERE ID = NEW.id; END;")[/code]
I can follow the code in the debugger and it works fine until I get to the db.ExecuteSQL("?CREATE TRIGGER line.
The database gets created.
The Table Help gets created.
But when I get to ExecuteSQL the line to create the Trigger, I get a database exception error.
I can open the database that is created in another application (I am using DB Browser for SQLite) that deals with SQLite and manually execute the code (SQL command):
CREATE TRIGGER when_change AFTER UPDATE ON Help BEGIN UPDATE Help SET ModifyLocal = datetime(CURRENT_TIMESTAMP,'localtime') WHERE ID = NEW.id; END;
That is accepted without complaint and it works in the way that I want.
But somehow when I try to execute this command within Xojo using the ExecuteSQL command, it fails.
Can someone explain this to me? Thanks for any help.