Carlos Conti wrote:At certain times we have seen ourselves in the situation where manual overrides had to be done. Moreover I am not fully sure all foreign keys are promptly configured. IN our app we use hibernate and so far everything works as expected. But I wouldn't bet much on the correctness of the whole thing.
That's why.
Carlos Conti wrote:But additionally we need to test some business rules too. Any ideas?
Prevention is better than cure.
Carlos Conti wrote:Hi Roel, and anyone this might interest. This link will be very useful in the context of this discussion:
http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/
Regards and if you don't mind Roel would be great if you could share your thoughts.
Brian Tkatch wrote:While it will do the job, i strongly suggest you avoid triggers unless there is no other way to do it. Triggers are a problem waiting to happen.
No more Blub for me, thank you, Vicar.
Roel De Nijs wrote:But of course, it's possible that you have some business rules which can't be enforced by these constraints. Then you have 2 options: either enforce it in the application (using a Validator class), or enfore it in the database (using a trigger). Both have their benefits and drawbacks. It's up to you to decide which one is the best fit for your needs. I can't make that decision for you.
Brian Tkatch wrote:Oracle (not sure about the others) allows you to leave bad data in place while restricting new bad data, via NOVALIDATE RELY. You may want to look into that option for the time being.
Martin Vajsar wrote:
Roel De Nijs wrote:But of course, it's possible that you have some business rules which can't be enforced by these constraints. Then you have 2 options: either enforce it in the application (using a Validator class), or enfore it in the database (using a trigger). Both have their benefits and drawbacks. It's up to you to decide which one is the best fit for your needs. I can't make that decision for you.
There's a third possibility, which can be handy in some circumstances: implement the business logic itself in a stored procedure. It can be especially appealing if the database is being accessed by more than one independent applications, or if there's a skilled DB guy who can take care of implementing and optimizing the queries in it, or if the business logic does a lot of data manipulation in the database (in which case doing it in the DB can be faster).
Triggers have similar benefits, as has already been mentioned, but doing data validation correctly using triggers is tremendously difficult in multi-user environment. Code in the triggers runs as part of a transaction, and therefore doesn't see changes made by other transactions, so two transactions running together might do changes in the database which are valid in isolation, but invalid when applied together. Possibility of different isolation levels complicates things even further. Don't do validation in triggers unless you know your database really, really well.
Martin Vajsar wrote:
Brian Tkatch wrote:Oracle (not sure about the others) allows you to leave bad data in place while restricting new bad data, via NOVALIDATE RELY. You may want to look into that option for the time being.
NOVALIDATE RELY isn't the way to go if there are data violating the constraints. In this case, you'd use NOVALIDATE NORELY (or simple NOVALIDATE, as NORELY is the default, for a good reason).
NOVALIDATE RELY tells Oracle to skip constraint validation, but assume that all data are valid (just because you said so - see how gullible it is?). In this case, the query optimizer will make assumptions about the data based on these constraints and might produce query plans that are incorrect when the data in fact violate the constraint. Wrong plan means wrong data being output by the database, and this kind of error is really difficult to find. (The reason to use this option is to save time the database would otherwise spent validating the constraint, if you know the constraint actually holds. It's used mostly in datawarehouses, where you've just built your billion-rows-table, you know it's correct, and you don't want to spend several hours validating the constraint.)
NOVALIDATE NORELY is used in situations like this: we know there are invalid data in the table, and we want to prevent new invalid data from being stored in the database ()as you said). The database knows that there might be data violating the constraint, and produces query plans accordingly.
This is Oracle, though. Not sure whether other database offers similar level of sophistication (read: amount of things that can go wrong).![]()