How to get BEFORE INSERT triggers to use the AUTO_INCREMENT value

21/04/16

Permalink 09:55:10 am, by mholmes, 556 words, 150 views   English (CA)
Categories: Activity log; Mins. worked: 60

How to get BEFORE INSERT triggers to use the AUTO_INCREMENT value

This is something I've struggled with for ages, and I've finally got a working solution, so I'm documenting it here with a real example, laid out the way you'd need to implement it through phpMyAdmin, since that's the most common interface we use. The problem arises when you want to auto-populate a field which will serve as a single-line descriptor for the record, suitable for use in drop-down lists when editing linked tables. One obvious requirement is to provide the primary key for the record. In a BEFORE UPDATE trigger this is easy, because you can just access NEW.`fieldName`, but in a BEFORE INSERT trigger you can't, because it hasn't been set yet. You can't use an AFTER UPDATE trigger because then you don't have access to the NEW record data. The solution is to read the AUTO_INCREMENT value for the table from the information_schema database. Here's an example:

[Run this first]

DROP TRIGGER IF EXISTS `landscapes_mapridgedev`.`own_desc_insert`;
[Set the delimiter to // in the phpMyAdmin interface before running this:] CREATE TRIGGER `landscapes_mapridgedev`.`own_desc_insert` BEFORE INSERT ON `landscapes_mapridgedev`.`owners` FOR EACH ROW BEGIN DECLARE next_id INT; SET next_id = (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE() AND `TABLE_NAME`='owners'); SET NEW.`own_desc` = CASE WHEN (LENGTH(NEW.`own_surname`) > 0 AND LENGTH(NEW.`own_forenames`) > 0 AND LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_surname`,_utf8', ',NEW.`own_forenames`,_utf8' : ',NEW.`own_street_num`,_utf8', ',NEW.`own_street`,_utf8' (',next_id,_utf8')') WHEN (LENGTH(NEW.`own_surname`) > 0 AND LENGTH(NEW.`own_forenames`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_surname`,_utf8', ',NEW.`own_forenames`,_utf8' : ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_surname`) > 0 AND LENGTH(NEW.`own_forenames`) > 0) THEN concat(NEW.`own_surname`,_utf8', ',NEW.`own_forenames`,_utf8' : (no address)',_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_surname`) > 0) THEN concat(NEW.`own_surname`,_utf8' (no forenames or address)',_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_institution_name`) > 0 AND LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_institution_name`,_utf8' : ',NEW.`own_street_num`,_utf8', ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_institution_name`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_institution_name`,_utf8' : ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_institution_name`) > 0) THEN concat(NEW.`own_institution_name`,_utf8' : (no address)',_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(_utf8' : (no name): ',NEW.`own_street_num`,_utf8', ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(_utf8' : (no name or street number): ',NEW.`own_street`,_utf8' (',@nextid,_utf8')') ELSE concat(_utf8'(no name or address)',_utf8' (',@nextid,_utf8')') END; END //

Pingbacks:

No Pingbacks for this post yet...

Adaptive Database

This project aims to design a flexible online database which can be set up as very quickly for a researcher requiring a relational database, and is easy to modify and extend. The Web GUI provides tools for data-entry in as simple and convenient a manner as possible, while also allowing data-integrity checks. The application development sandbox is currently at https://hcmc.uvic.ca/people/martin/potluck/editor/index.php; and half a dozen UVic projects are already using the codebase. The code is managed through SVN here: https://revision.hcmc.uvic.ca/svn/adaptivedb/.

Reports

XML Feeds