In particular, you cannot have two triggers for a table that have the same activation time and activation event. This is also advantageous if you want to invoke the same routine from within several triggers.My SQL triggers can be used to create some validation conditions that are a little bit more complex than what can be achieved with basic data types and unique index for example.
Support for triggers is included beginning with My SQL 5.0.2.A trigger is a named database object that is associated with a table and that is activated when a particular event occurs for the table.My SQL 5.5 introduced the handy operator that can be used to do just that.It allows to set a specific error condition (or SQLSTATE) and a custom error message.This is exactly what we need in this case, as it will return a native My SQL error and thus also prevent the insert or update clause (as long as we use it in a trigger that is specified to run before insert or update). If you are still running old versions, the statement when creating the trigger so you can’t use that.
A simple trick is to call a non existing stored procedure to trigger a My SQL error.If you use some generic name for the non existing procedure, like , you will not be getting a very useful error message so it can be quite confusing especially for people that do not know about this trigger and the constraint it enforces.This can be improved a little bit by using the error message in the actual call, so the error message becomes a little bit more relevant.There cannot be two triggers for a given table that have the same trigger action time and event.For example, you cannot have two CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 ); DELIMITER | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 1 WHERE a4 = NEW.a1; END; | DELIMITER ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0); In addition, triggers created in My SQL 5.0.16 or later cannot be dropped following a downgrade to My SQL 5.0.15 or earlier.The trigger becomes associated with the table named was added in My SQL 5.0.2.