As MySQL triggers are now available in the community edition I decided to sort a small but irritating problem (not sure how long but last time I checked they weren’t).
I needed all entries to a field to be stored in upper case. Easy enough in PHP but there will be situations when entries will come from sources I have no control over, a trigger would be ideal, or so I thought:
Can’t update table ‘tbl’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger
Fair enough, that message suggests that because I am already working on the table I cannot perform the task I want. Probably the table is locked, but I want to update a field entry before it is updated, thus why can I not make the change before its locked.
Well I can’t.
MySql’s documentation had a problem today, a ‘recursive redirect’ so I could not find anything there to help. I resorted to a dusty copy of ‘MySQL Database’ and discovered the NEW object contains all the field data and thus can be updated before saved to the table. Here’s an example:
delimiter | CREATE TRIGGER ucase_emails_code BEFORE UPDATE ON emails FOR EACH ROW BEGIN SET NEW.code = UCASE(NEW.code); END; |
