hPv1RV5U

1DROP TRIGGER IF EXISTS gene_lcase_insert;
2DROP TRIGGER IF EXISTS morph_lcase_insert;
3DROP TRIGGER IF EXISTS genus_lcase_insert;
4DROP TRIGGER IF EXISTS species_lcase_insert;
5DROP TRIGGER IF EXISTS python_lcase_insert;
6DROP TRIGGER IF EXISTS python_extract_year_insert;
7
8/* Enforce lower-case on INSERT into 'gene' table */
9
10CREATE TRIGGER gene_lcase_insert BEFORE INSERT ON gene
11FOR EACH ROW
12    SET NEW.name = LOWER(NEW.name);
13
14/* Enforce lower-case on INSERT into 'morph' table */
15
16CREATE TRIGGER morph_lcase_insert BEFORE INSERT ON morph
17FOR EACH ROW
18      SET NEW.name = LOWER(NEW.name); 
19
20/* Enforce lower-case in INSERT into 'genus' table */
21
22CREATE TRIGGER genus_lcase_insert BEFORE INSERT ON genus
23    FOR EACH ROW
24     SET NEW.name = LOWER(NEW.name);
25
26/* Enforce lower-case on INSERT into 'species' table */
27
28CREATE TRIGGER species_lcase_insert BEFORE INSERT ON species
29  FOR EACH ROW
30      SET NEW.name = LOWER(NEW.name);
31
32/* Enforce lower-case on INSERT into 'python' table */
33
34CREATE TRIGGER python_lcase_insert BEFORE INSERT ON python
35  FOR EACH ROW
36      SET NEW.sold_to = LOWER(NEW.sold_to),
37          NEW.feeding = LOWER(NEW.feeding);
38
39/* Extract year from date_of_birth on INSERT if not NULL */
40
41DELIMITER //
42CREATE TRIGGER python_extract_year_insert BEFORE INSERT ON python
43   FOR EACH ROW
44   BEGIN
45          IF NEW.date_of_birth IS NOT NULL
46              THEN
47                  SET NEW.sequence_year =  YEAR(NEW.date_of_birth);
48          END IF;
49  END//
50DELIMITER ;
51	
52ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
53	
54mysql> select trigger_name from information_schema.triggers;
55+----------------------+
56| trigger_name         |
57+----------------------+
58| gene_lcase_insert    |
59| genus_lcase_insert   |
60| morph_lcase_insert   |
61| python_lcase_insert  |
62| species_lcase_insert |
63+----------------------+
645 rows in set (0.00 sec)
65	
66mysql> drop trigger python_extract_year_insert;
67ERROR 1360 (HY000): Trigger does not exist