[New to DB triggers/transactions]
In my app, my users are allowed to create, edit and delete rooms. In the event that a room with items assigned to it gets removed, I want to default their room location to a generic room called "unassigned." Right now I'm doing this in the application level. My question is, should I be doing this in the DB level? If this should indeed occur in MySQL using triggers, how do I go about making my app aware of any failures or successes?
Right now I'm doing this in the application level
That's usually best.
Code belongs in the application.
Data belongs in the database.
Code does not belong in the database, any more than data belongs in the application code.
Truggers are generally a very bad idea because they fragment the processing logic into two places -- the application and the database.
It's often best to have the application logic in one place. Outside the database.
The data is the most valuable part of the application. Don't pollute it with triggers or other bits of application logic.
[This answer will be downvoted by folks who like stored procedures and triggers in the database.]
"Should" is a subjective issue. Does it work fine now? If so, why mess with it?
Does this sound like something you want to get involved with?
Personally, I would do business logic at the application layer. As you've pointed out, your business application already has to know whether or not the room is assigned or unassigned. You aren't losing any efficiency by doing this at the application layer, and your code will be easier to read, since it will all be in one spot.
Personally, I'll use triggers almost exclusively for database functions such as logging changes to a record. Triggers are ideal for this, and by using triggers, you are guaranteed that any changes to a record will be acted on regardless of what the business layer is doing.
