I've got a simple question: I was wondering if it is possible to (temporary or permanent if possible) block the insertion of a record into a table with a specific value for a specific column. I'm using mysql with InnoDB
So I have this table Cart, which contains some columns and one of them is CartID (it is not the primary key thou). And I would like to block insertions in the table Cart with a specific CartID (let's say 100), while I'm doing work on another table in the DB.
The most powerful approach is to wrap all the basic operations like INSERT, DELETE, UPDATE and SELECT into the set of the stored routines. That acts like an API to the database and can be extended by any desired checkouts/conversions/calculations you ever want. If parameters passed to the stored routine meet some conditions you can INSERT them. Otherwise you reject those data without inserting.
Same effect can be achieved by trigger BEFORE INSERT that look like this:
BEGIN
IF NEW.id IN (100, 200, 300, 456)
THEN SET NEW.id = 1;
END IF;
END
where id=1 is already existent record and id column have UNIQUE constraint. But triggers in general are unclear and non-transparent tool that can be very difficult to debug. Also autoincrements and triggers can interfere with unforeseen consequencies so it's better to avoid triggers as possible.
