#18 CREATE TRIGGER / multiple / comments

open
nobody
None
5
2004-11-08
2004-11-08
Alexander Graef
No

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

Discussion

  • SQL database description / script

     
    Attachments
  • 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

     
  • CS-Class file in which the error occurs

     
    Attachments
  • 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