#18 CREATE TRIGGER / multiple / comments

[Alexander Graef]

I had the problem described in Bug Tracker Item
1041924. I checked out the latest CVS, but the
problem only changed. Basically, when initializing
the database, I read a .sql-File and put the
contents directly into CommandText. The file works
the following way:

Database.sql
BEGIN TRANSACTION;

CREATE TABLE TableName ( ... );
CREATE INDEX IX_TableName_Rowname ON
TableName (Rowname ASC);

CREATE TRIGGER RowInsert AFTER
INSERT ON TableName
FOR EACH ROW BEGIN
UPDATE OtherTable SET
OtherRowName=OtherRowName+1 WHERE
NEW.ForeignKey = PrimaryKey;

COMMIT;

The code that runs the whole thing is this one:
Database.cs

// Open database
Connection=new SQLiteConnection("Data
Source=Database.db;New=True;Version=3;Synchron
ous=Off;Cache Size=16384");
Connection.Open();

// Create database file and tables
StreamReader stmreader=new StreamReader
(File.OpenRead("Database.sql"));
SQLiteCommand cmd = Connection.CreateCommand
();
cmd.CommandText = stmreader.ReadToEnd();
cmd.ExecuteNonQuery();

// Store data
SQLiteTransaction transaction=(SQLiteTransaction)
Connection.BeginTransaction();
....

However, when execution comes
to "Connection.BeginTransaction()" SQLite tells me,
that the transaction cannot be started, because
another one is in Progress:

Finisar.SQLite.SQLiteException: cannot start a
transaction within a transaction

This means, that the "COMMIT;"-statement from
Database.sql was not executed. If I simply
comment out the "CREATE TRIGGER"-block, the
same error occurs. When I cut out the
whole "CREATE TRIGGER"-block, all runs fine. It
seems SQLite is quitting execution after the "END;"
statement, without further evaluating the rest,
even if the statement is commented out. The SQL-
File is attached below. If I copy and paste the file
into sqlite3explorer and execute it, no errors will
show up.

Thank you and regards
Alexander Gräf

[Alexander Graef]

SQL database description / script

[Alexander Graef]

Logged In: YES
user_id=1030440

Update: When manually executing every single CREATE
TRIGGER statement, it also fails on the last one
because the execution will get cut after "END;":

cmd.CommandText="CREATE TRIGGER TrackInsert
AFTER INSERT ON Tracks FOR EACH ROW BEGIN "+
"UPDATE Albums SET TrackCount=TrackCount+1 WHERE
NEW.AlbumID = AlbumID; END;";
cmd.ExecuteNonQuery();

cmd.CommandText="CREATE TRIGGER TrackUpdate
AFTER UPDATE OF AlbumID ON Tracks FOR EACH ROW
BEGIN "+
"UPDATE Albums SET TrackCount=TrackCount-1 WHERE
OLD.AlbumID = AlbumID; END; "+
"UPDATE Albums SET TrackCount=TrackCount+1 WHERE
NEW.AlbumID = AlbumID; END; ";
cmd.ExecuteNonQuery();

Only the last statement fails ("Syntax error near
UPDATE"). See attached file.

Regards, Alexander Grf

[Alexander Graef]

CS-Class file in which the error occurs

[Alexander Graef]

Logged In: YES
user_id=1030440

Update: That was my error :-(, the first "END;"-
statement is the error. However, the first error persists.

Thanks, Alexander Grf