There are 2 tables

db.execSQL("Create table STUDGROUPS (" + BaseColumns._ID + " integer primary key autoincrement, faculty text, course int, name text, head text," + "constraint name_u unique (name) on conflict abort);"); db.execSQL("Create table Students (" + BaseColumns._ID + " integer primary key autoincrement, name text, IDGROUP int," + "constraint idgroup_fk foreign key(IDGROUP) references STUDGROUPS(_id) on delete cascade on update cascade);");

The group should not be more than 6 students and less than 3, i.e. it is necessary to close the insert, if there are with the same group id 6 of students and deletion, there will be less than 3 people in the group.

An important condition is to save the cascade. Therefore, when removing a student, you need to check whether the group has been deleted. If yes, then restrictions on less than 3 are removed and the student is removed, if not, the deletion is prohibited.

Help to write such triggers. Help!

    1 answer 1

     create trigger prevent_big_groups before insert on students begin select case when ( select count(*) from students where idgroup = NEW.idgroup ) = 5 then raise(ABORT, 'Too much students in the group') end; end; create trigger prevent_small_groups before delete on students begin select case when ( select count(*) from students where idgroup = OLD.idgroup ) = 3 then raise(ABORT, 'Not enough students in the group') end from studgroups where id = OLD.idgroup; end;