Until Maria DB 10.2.3, a table could have only one trigger defined for each event/timing combination: for example, a table could only have one BEFORE INSERT trigger.
The LOAD DATA INFILE and LOAD XML statements invoke INSERT triggers for each row that is being inserted.
After selecting all records in the table, notice how the Order Approval Date Time for the second row does not equal the Order Approval Date Time for the first row.
Again, this is our desired behavior and all appears well.
If you're entering multiple statements on the command line, you'll want to temporarily set a new delimiter so that you can use a semicolon to delimit the statements inside your trigger. DROP TABLE animals; UPDATE animal_count SET animals=0; CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, name char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=Inno DB; DELIMITER // CREATE TRIGGER the_mooses_are_loose AFTER INSERT ON animals FOR EACH ROW BEGIN IF = 'Moose' THEN UPDATE animal_count SET animal_count.animals = animal_count.animals 100; ELSE UPDATE animal_count SET animal_count.animals = animal_count.animals 1; END IF; END; // DELIMITER ; INSERT INTO animals (name) VALUES('Aardvark'); SELECT * FROM animal_count; --------- | animals | --------- | 1 | --------- INSERT INTO animals (name) VALUES('Moose'); SELECT * FROM animal_count; --------- | animals | --------- | 101 | --------- If a trigger contains an error and the engine is transactional, or it is a BEFORE trigger, the trigger will not run, and will prevent the original statement from running as well.
However, we need to run a second test to ensure that the correct number of rows have been updated.
In our second test, we will execute a T-SQL UPDATE statement to set the Order Status value to "Approved" for the second row in the table (pk ID = 2).
The REPLACE statement is executed with the following workflow: Otherwise, it works like a normal INSERT statement.
Note that TRUNCATE TABLE does not activate any triggers.