Use trigger to update ENUM field in another table

Is it possible to define a trigger on table A, which updates the enum values of table B based on the values in table A?

If I’ve got an enum field in my products table for ‘productType’

alter table products add productType enum('BSK', 'CK','PDD')

, and I want to store a more textual representation of these codes in a productTypes table

CREATE TABLE productTypes
(
	typeCode VARCHAR(10),
	typeText VARCHAR(200)
)
INSERT INTO productTypes VALUES("BSK","Biscuits"), ("CK","Cakes"),("PDD","Puddings")

If I want another productType value, I’d have to insert it into productTypes, and change the productType enum in products. Can I use a trigger to do this whenever the prodyctTypes table is changed?

i suppose you could, if you really wanted to, but why?

since you already have a producttypes table in mind, which will contain all the valid codes, you really shouldn’t be using an evil ENUM

use a foreign key instead – same data integrity, but also portable to other database systems (ENUM is non-standard)…

… and no trigger necessary

:slight_smile: