> From: Simon Davies <[EMAIL PROTECTED]>> Date: 2008/9/16> Subject: Re:
> [sqlite-announce] Foreign key trigger in transaction> triggers wrongly> To:
> [EMAIL PROTECTED], [EMAIL PROTECTED]> > > 2008/9/16 Björn Rauch <[EMAIL
> PROTECTED]>:> > Hello,> .> .> .> > This looks to me like the trigger does not
> see the changes made within the> > transaction, i.e. the new row in the table
> Document. Am I doing something> > wrong? Is this unexpected behavior?> >> >
> Thanks in advance for your help!> >> > Bjorn> >> > -> > > Hi,> > SQLite
> version 3.4.2> Enter ".help" for instructions> sqlite>> sqlite> CREATE TABLE
> [Document]> ...> (> ...> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,> ...>
> [Title] TEXT(200)> ...> );> sqlite>> sqlite> -- primary key read only>
> sqlite>> sqlite> CREATE TRIGGER [pk_Document]> ...> BEFORE UPDATE OF [Id] ON
> [Document]> ...> FOR EACH ROW> ...> BEGIN> ...> SELECT RAISE( ABORT, 'Primary
> key invariant: pk_Document' );> ...> END;> sqlite>> sqlite> -- table File>
> sqlite>> sqlite> CREATE TABLE File> ...> (> ...> Id INTEGER PRIMARY KEY
> AUTOINCREMENT,> ...> DocumentId INTEGER, -- foreign key (Document.Id)> ...>
> RootFolder TEXT(100), -- foreign key (Storage.RootFolder)> ...> FileName
> TEXT(250),> ...> FullName TEXT(500),> ...> Path TEXT(250)> ...> );> sqlite>>
> sqlite> -- primary key read only> sqlite>> sqlite> CREATE TRIGGER [pk_File]>
> ...> BEFORE UPDATE OF [Id] ON [File]> ...> FOR EACH ROW> ...> BEGIN> ...>
> SELECT RAISE( ABORT, 'Primary key invariant: pk_File' );> ...> END;> sqlite>>
> sqlite> -- foreign key File.DocumentId --> Document.Id> sqlite>> sqlite>
> CREATE TRIGGER [fk_File_Document_del]> ...> BEFORE DELETE ON [Document]> ...>
> FOR EACH ROW> ...> WHEN (old.[Id] IN (SELECT [DocumentId] FROM [File] GROUP>
> BY [DocumentId]))> ...> BEGIN> ...> SELECT RAISE( ABORT, 'Foreign key
> violated: fk_File_Document_del' );> ...> END;> sqlite>> sqlite> CREATE
> TRIGGER [fk_File_Document_ins]> ...> BEFORE INSERT ON [File]> ...> FOR EACH
> ROW> ...> WHEN (new.[DocumentId]<>0 AND new.[DocumentId] NOT IN> (SELECT [Id]
> FROM [Document]))> ...> BEGIN> ...> SELECT RAISE( ABORT, 'Foreign key
> violated: fk_File_Document_ins' );> ...> END;> sqlite>> sqlite> CREATE
> TRIGGER [fk_FileDocument_upd]> ...> BEFORE UPDATE ON [File]> ...> FOR EACH
> ROW> ...> WHEN (new.[DocumentId] NOT IN (SELECT [Id] FROM [Document]))> ...>
> BEGIN> ...> SELECT RAISE( ABORT, 'Foreign key violated: fk_File_Document_upd'
> );> ...> END;> sqlite>> sqlite>> sqlite> --> sqlite> -- Insert some sample
> data> sqlite> --> sqlite> BEGIN;> sqlite> INSERT INTO [Document] ([Title])
> VALUES ('blabla');> sqlite> INSERT INTO File VALUES( 422, 0, 'root folder',
> 'file name',> 'full name', 'path' );> sqlite>> sqlite>> --> -- Not sure why
> you have included this next select...> --> sqlite> SELECT [seq] FROM
> [sqlite_sequence] WHERE [name]='Document';> 1> sqlite>> sqlite> --> sqlite>
> -- What have we got?> sqlite> --> sqlite> select * from document;> 1|blabla>
> sqlite> select * from file;> 422|0|root folder|file name|full name|path>
> sqlite>> sqlite>> sqlite> --> sqlite> -- Now try the update> sqlite> -->
> sqlite> UPDATE [File] SET [DocumentId]=1 WHERE [Id]=422;> sqlite>> sqlite>
> COMMIT;> sqlite>> > works fine...> > Of course, if I delete all entries from
> Document and File, then run> the script again it will fail, because the next
> time through the> Document record will have DocumentId of 2.> > Rgds,> Simon
Yes, indeed it works this way. But I am executing these commands from a .NET
application using SQLite.NET (SQLite for ADO.NET 2.0,
http://sqlite.phxsoftware.com) to make these calls. Transaction handling seems
to work there, but the trigger is raised as I explained.
Of course, maybe the ADO.NET provider implementation might be the culprit.
Regards,
Bjorn
_
Invite your mail contacts to join your friends list with Windows Live Spaces.
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create_url=/friends.aspx=en-us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
