Database triggers that upon deletion deletes other data


I’m creating some database triggers to get rid of excess data. For example, when a user is deleted, delete everything related to it, for example chats, interests and so on.

This seems like easy enough of a task, but isn’t working.

I have a data type for chats and a data type for messages. Chat contains a list of messages, every message has a “chat” attribute. When a chat is deleted, the messages should also be deleted.

Here’s my workflow, which doesn’t work. After I delete the chat (I’m doing it straight in the database, not on a page using a button or anything), the messages still remain, only their “chat” attribute shows “deleted thing”, as it should without database triggers.

Any ideas what I’m doing wrong here?

I’m not going to answer your question directly, as I personally still struggle with the database triggers event, and what data might be available and what won’t be…as such here it seems like possibly the data required to search messages is gone since the related chat would have been deleted, although the database triggers function would have you assume the chat before change is still available.

I don’t have an answer for if it is removed or still there for reference, but you could try to search the messages using constraints whose connected chat has the unique id of the chat before change.

As a side note, you may consider the implications for the other chat conversation participant who will also have these messages deleted only because the conversation participant involved deleted their own account…just because that happens doesn’t necessarily mean the other participant wants to delete all the messages.

In my own messaging systems I don’t delete the messages, and instead just remove the deleted user from the participant list, so the other user still access the messages.

Yeah, the reason I want to delete chats is actually just for the test-version so I don’t manually have to delete messages and chats separately when testing something. :slight_smile: