Android的SQLite触发器操作经历 [英] Android Sqlite trigger operation go through

问题描述

这是我的第一个表有3列，所有的用户输入。无并发症，它工作正常。但还有另一种表，它是依赖于该表的第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+ "));");

正如你看到的是越来越成功创建表。 现在我要编写一个触发器声明的基础上，当我从第一个表中删除房号，然后在第二个表，有相同的房间号码应删除所有行。并且如果我在第一表中添加新的房间号码，那么它应该在第二表来添加。 我已经写了新的房间号将触发器声明如下

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;");

这一块code我加在我的数据库文件的OnCreate（）。还有什么我做什么？什么也没有发生在这里。 请您指出我在哪里，我会犯错。 在此先感谢吨， 斯海斯塔

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

推荐答案

您已经添加了一个插入触发。这就提出了一个错误，如果你试图插入的行不会在触发指定的约束相匹配;它确实一无所知从一个表中删除行时从其他行被删除。

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.

您需要在其上删除相应的行的第一个表中的后删除触发。

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

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