mysql and trigger usage question
- by dhruvbird
I have a situation in which I don't want inserts to take place (the transaction should rollback) if a certain condition is met. I could write this logic in the application code, but say for some reason, it has to be written in MySQL itself (say clients written in different languages will be inserting into this MySQL InnoDB table) [that's a separate discussion].
Table definition:
CREATE TABLE table1(x int NOT NULL);
The trigger looks something like this:
CREATE TRIGGER t1 BEFORE INSERT ON table1
FOR EACH ROW
IF (condition) THEN
NEW.x = NULL;
END IF;
END;
I am guessing it could also be written as(untested):
CREATE TRIGGER t1 BEFORE INSERT ON table1
FOR EACH ROW
IF (condition) THEN
ROLLBACK;
END IF;
END;
But, this doesn't work:
CREATE TRIGGER t1 BEFORE INSERT ON table1 ROLLBACK;
You are guaranteed that:
Your DB will always be MySQL
Table type will always be InnoDB
That NOT NULL column will always stay the way it is
Question: Do you see anything objectionable in the 1st method?
