On delete cascade in one table

Question

My module is about user permissions.

An user can own a device. An user can give permissions for this device to another user. There are no read/write permissions: if you have permissions you can do the same as everyone other with permissions => children can give permissions to.

permission inheritance: 
A gives B permission. B gives C permission. C gives D permission.

a --> b --> c --> d

table: USER_DEVICE
 ---------------------------------
| FK_USER | FK_DEVICE | FK_PARENT |
|---------|-----------|-----------|
| a       | d         | null      |
| b       | d         | a         |
| c       | d         | b         |
| d       | d         | c         |
 ---------------------------------

When user a removes the permission for device d on user b every children should be deleted recursive.

DELETE USER_DEVICE WHERE FK_DEVICE = 'd' AND FK_USER = 'b'

should trigger

DELETE USER_DEVICE WHERE FK_DEVICE = 'd' AND FK_PARENT = 'b'

should trigger

DELETE USER_DEVICE WHERE FK_DEVICE = 'd' AND FK_PARENT = 'c'

Is it possible to create this behavior as a delete cascade?

Answer 1

EDIT: A foreign key with ON DELETE CASCADE works just fine. See joops answer for details.

An ON DELETE CASCADE works for foreign keys, but not for these kinds of constraints. Instead, you could create an AFTER DELETE trigger for your table which deletes the dependent rows.