0

I have the following script which drops/ creates six triggers for a MySQL database; v5.5.52.

DROP TRIGGER IF EXISTS gene_lcase_insert;
DROP TRIGGER IF EXISTS morph_lcase_insert;
DROP TRIGGER IF EXISTS genus_lcase_insert;
DROP TRIGGER IF EXISTS species_lcase_insert;
DROP TRIGGER IF EXISTS python_lcase_insert;
DROP TRIGGER IF EXISTS python_extract_year_insert;

/* Enforce lower-case on INSERT into 'gene' table */

CREATE TRIGGER gene_lcase_insert BEFORE INSERT ON gene
FOR EACH ROW
    SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'morph' table */

CREATE TRIGGER morph_lcase_insert BEFORE INSERT ON morph
FOR EACH ROW
      SET NEW.name = LOWER(NEW.name); 

/* Enforce lower-case in INSERT into 'genus' table */

CREATE TRIGGER genus_lcase_insert BEFORE INSERT ON genus
    FOR EACH ROW
     SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'species' table */

CREATE TRIGGER species_lcase_insert BEFORE INSERT ON species
  FOR EACH ROW
      SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'python' table */

CREATE TRIGGER python_lcase_insert BEFORE INSERT ON python
  FOR EACH ROW
      SET NEW.sold_to = LOWER(NEW.sold_to),
          NEW.feeding = LOWER(NEW.feeding);

/* Extract year from date_of_birth on INSERT if not NULL */

DELIMITER //
CREATE TRIGGER python_extract_year_insert BEFORE INSERT ON python
   FOR EACH ROW
   BEGIN
          IF NEW.date_of_birth IS NOT NULL
              THEN
                  SET NEW.sequence_year =  YEAR(NEW.date_of_birth);
          END IF;
  END//
DELIMITER ;

Upon executing the script, I receive the following error - when the final 'CREATE TRIGGER' statement is reached:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

I then perform the following check, to see if the TRIGGER exists:

mysql> select trigger_name from information_schema.triggers;
+----------------------+
| trigger_name         |
+----------------------+
| gene_lcase_insert    |
| genus_lcase_insert   |
| morph_lcase_insert   |
| python_lcase_insert  |
| species_lcase_insert |
+----------------------+
5 rows in set (0.00 sec)

It appears that the final 'python_extract_year_insert' trigger is not being created; sanity check:

mysql> drop trigger python_extract_year_insert;
ERROR 1360 (HY000): Trigger does not exist

I am uncertain as to why this is occurring. Thanks in advance.

Note:

I have tried implementing the final trigger (python_extract_year_insert) utilizing a separate file, to see if it was a processing issue of some kind - to no avail.

0

The answer is in your question:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table

For any given table you can only have one trigger per each event (insert/update/delete) AND per each time of execution of said event (after/before).

  • This is no longer the case in MySQL 5.7, just for reference. – Philᵀᴹ Oct 5 '16 at 19:32
0

Combine the last two triggers since they are both BEFORE INSERT ON python.

Do you really need to lowercase everything? If your COLLATION for the columns is ..._ci, then comparisons will match upper and lower case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.