There is a problem using ON DELETE CASCADE on foreign keys in a SQL database if there are multiple paths from the root foreign key to the leaf. The way around this seems to be to replace the ON DELETE CASCADEs with INSTEAD OF DELETE triggers.
However several Stack Overflow posts (e.g. here, here), and here suggest using triggers only as "a last resort". Why?
I believe there is distrust of triggers because once they are created they are not well displayed in various interfaces, and don't communicate they are being run in very user friendly ways.
This leads to a feeling of 'magic' where things can happen in your database that you don't expect and are not made aware off. The deleting of more than you expect being perhaps the most frightening side effect.
Like so many things if they are abused they can be horrible, but they are very powerful if used well.
If I had 3 wishes to make my life in IT easier, top of the list would be to force developers to do DB training. I see so much ignorance around various RDBMSs it is frightening.
Triggers are not as some would believe "hidden away" they are freely visible and do as they are programmed to. Neither do they run behind the scenes nefariously. They run without getting in the way of the main processing - exactly what they are designed to do.
What is undoubtedly true is that they tend to be the last place you think to look for support queries etc because they are used so rarely. This is just human nature.
If you're worried about rows being quietly deleted without you being informed, there are many options open to you e.g. output deleted.* or you can simply flag records as deleted rather than deleting them outright (depending on storage limitations of course).
