Some times, we store valuable data that we would never want to lose. This is a particular fact when working with data for scientific, statistical purposes, like environmental analysis.
In such a situation, we can rely on our software, but… what happens if any error is introduced on the application layer? Some valuable registers could be lost and hard to identify and recover later. To avoid this effect, we’d rather prefer to implement some constraints in our database. I wouldn’t think that all the applications that connect to our database are going to be free of bugs, and our data is so valuable!
A good way to implement this protection is using triggers. Triggers are SQL sentences that execute when (or after, or before) some action happens over the registers of a table. Usually, they can be defined to be executed BEFORE or AFTER an UPDATE, INSERT or DELETE action happens.
So, the approach is: each time a register is told to be deleted from a certain table, the trigger we are going to set up will raise and tell the user (application, interface, whatsoever): ‘hey, listen, you cannot do that’. This trigger will be executed after the DELETE action, and will break the operation avoiding the deletion.
Unfortunately, this is quite easy in other database servers like Oracle and MSSQL, where exceptions and errors can be handled and created by the user. With these servers, the content of the trigger would like more or less like this:
throw_exception('cannot delete registers from this table');
And that’s all: the exception makes the entire request fail.
But we don’t have this feature in MySQL (at least not in 5.1 version), and the way to get the same behavior is a bit tricky. If we cannot send an exception to alert the user that something has failed… well, at least we would be able to make some thing crash, and the server itself will tell the user about this failure!
I guess that there are several ways of doing the same. This is my particular one: I try to insert empty values on the same table a register is about to be deleted. This throws an error because a trigger cannot modify the same table it has been raised from, and operation fails. An external user will not know, however that this error is due to the trigger I’ve added but OK, I’ll tell them. At least, I’ve prevented my table to lose data if it is not correctly managed by some applications. This is the code:
DELIMITER //
DROP TRIGGER IF EXISTS no_deletion_data;
CREATE TRIGGER no_deletion_data
BEFORE DELETE ON `Data`
FOR EACH ROW BEGIN
INSERT INTO `Data` VALUES();
END;//
DELIMITER ;
I know is not the smartest way to solve the problem, but at least is the cleanest I’ve got. Any suggestions? Well, at least, it works! 🙂