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 //