Hello, I am writing an application with the database layer implemented with
SQLite.NET. I am using SQLite 3.6.1 for Windows. I have 2 tables, Document and
File, where File has a foreign key to the autoincrement primary key of
Document. I enforce referential integrity using a set of foreign key triggers.
The table File is first filled with records where DocumentId=0. Then document
records are created based on a complicated algorithm involving aggregations on
all files. After creating a document, the appropriate files are updated to
point to the document. The inserts and updates run in a transaction. The update
of the File table raises the update trigger. It seems the the trigger is not
seeing the inserted records in the Document table. 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 -----Here are the excerpts of the
database script and statements that raise the trigger: -- table Document CREATE
TABLE [Document]( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Title]
TEXT(200)); -- primary key read onlyCREATE TRIGGER [pk_Document] BEFORE
UPDATE OF [Id] ON [Document] FOR EACH ROWBEGIN SELECT RAISE( ABORT,
'Primary key invariant: pk_Document' );END;-- table FileCREATE 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)); -- primary key
read onlyCREATE TRIGGER [pk_File] BEFORE UPDATE OF [Id] ON [File] FOR
EACH ROWBEGIN SELECT RAISE( ABORT, 'Primary key invariant: pk_File' );END;--
foreign key File.DocumentId --> Document.Id 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; 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; 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;The File table is filled with rows where the
DocumentId is 0 (a foreign key violation permitted by the insert trigger). My
application then executes the following SQL statements (aggregated): BEGIN
TRANSACTION INSERT INTO [Document] ([Title]) VALUES ('blabla') SELECT [seq]
FROM [sqlite_sequence] WHERE [name]='Document' UPDATE [File] SET [DocumentId]=1
WHERE [Id]=422 Now the UPDATE fails as the update trigger raises an abort:
Foreign key violated: fk_File_Document_upd -----
_________________________________________________________________
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
