Android Sqlite触发操作通过 [英] Android Sqlite trigger operation go through

问题描述

这是我的第一个表有3列，全部由用户输入。没有并发症，它工作正常。但是还有另一个表，它依赖于这个表第三列。即在房间号列上。请忽略名称约定，因为我没有真正遵循任何命名约定。

This is my first table it has 3 columns and all are entered by the user. No complication and it works fine. But there is another table and it is dependent on this table 3rd column. i.e. on room number column. Please ignore the names convention as i have not really followed any naming conventions.

这是我的第二个表，你可以看到它有最后一列作为房间号。请忽略名称约定。这个房间号列是外键，它依赖于第一个表条目或删除
现在我已经为第二个表创建了一个模式。它声明它有5列，最后一个是外键。

This is my second table and as you can see it has its last column as room number. please ignore name convention. this room number column is foreign key it is dependent on the 1st table entry or delete Now I have created a schema for the 2nd table. which declares that it has 5 columns and the last one is the foreign key.

 private static final String DATABASE_CREATE_NOTES_ID_TABLE =
    ("CREATE TABLE "+ DATABASE_NOTES_TABLE +" ("+ KEY_ROWID +" INTEGER PRIMARY KEY AUTOINCREMENT, "+
            KEY_TIME + " TEXT, "+ 
            KEY_NOTES + " TEXT, "+ 
            KEY_DATE +  " TEXT, " +
            KEY_ROOMS + " INTEGER , FOREIGN KEY (" + KEY_ROOMS + ") REFERENCES " + DATABASE_PATIENT_TABLE +" (" +KEY_ROOM_NUMBER+ "));");

您看到的表格已成功创建。
现在，我必须写一个TRIGGER语句，基于这一点，当我从第一个表中删除一个房间号，然后第二个表中具有相同房间号的所有行应该被删除。如果我在第一个表中添加一个新的房间号，那么它应该被添加到第二个表中。
我写的新房间号插入的触发语句如下

Tables as you see is getting created successfully. Now I have to write a TRIGGER statement, based on that when I delete a room number from the first table, then all the rows in the 2nd table which has the same room number should be deleted. And If I add a new room number in the first table then it should be added in the 2nd table. The trigger statement for insert of new room number that I have written is as follows

    db.execSQL("CREATE TRIGGER fk_notesTable_roomNumber " +
                " BEFORE INSERT "+
                " ON "+DATABASE_NOTES_TABLE+
                " FOR EACH ROW BEGIN"+
                " SELECT CASE WHEN ((SELECT "+KEY_ROOM_NUMBER+" FROM "+DATABASE_PATIENT_TABLE+" WHERE "+KEY_ROOM_NUMBER+"=new."+KEY_ROOMS +" ) IS NULL)"+
                " THEN RAISE (ABORT,'Foreign Key Violation') END;"+
                "  END;");

我在我的数据库文件的onCreate（）中添加了这段代码。我还要做什么？这里没有发生。
请指出我在哪里犯错误。
感谢吨，
Shaista

This piece of code I have added in the onCreate() of my database file. What else do I do ? Nothing is happening here. Please kindly point me out where I am making mistake. Thanks in tons, Shaista

推荐答案

您添加了 INSERT 触发器。如果尝试插入与触发器中指定的约束不匹配的行，则会出现错误;

You have added an INSERT trigger. This raises an error if you try and insert a row that doesn't match the constraint specified in the trigger; it does nothing about deleting rows from one table when rows from another are deleted.

您需要一个 AFTER DELETE 触发器在删除相应行的第一个表上。

You need an AFTER DELETE trigger on the first table which deletes the corresponding rows.

这篇关于Android Sqlite触发操作通过的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！