Updating of old row is not allowed in trigger
Triggers in different schemas can have the same name.In addition to the requirement that trigger names be unique for a schema, there are other limitations on the types of triggers you can create.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.
SQL DELETE FROM trigger_test; BEFORE STATEMENT - DELETE BEFORE EACH ROW - DELETE (old.id=2) AFTER EACH ROW - DELETE (old.id=2) BEFORE EACH ROW - DELETE (old.id=1) AFTER EACH ROW - DELETE (old.id=1) AFTER STATEMENT - DELETE 2 rows deleted.
Remarks regarding binary logging as it applies to triggers are given in Section 21.4, “Binary Logging of Stored Routines and Triggers”.
For answers to some common questions about triggers in My SQL 5.0, see Section A.5, “My SQL 5.0 FAQ — Triggers”. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.
Triggers for a table are also dropped if you drop the table.
Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema.
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.