I have this trigger that isn't working...
CREATE TRIGGER receipt_serial_create AFTER INSERT ON receipt
UPDATE receipt r SET r.serial = ( SELECT MAX( serial )
WHERE `type` = NEW.`type` ) +1
WHERE receipt_id = NEW.receipt_id;
And I'm getting this error
Can't update table 'receipt' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. in /home/mmorris/htdocs/clerk/includes/classes/MySQL.php on line 80
For anyone curious, I've been asked to make an incremental id for our receipts by type. I consider the request obnoxious because up till now we've simply displayed the primary key which auto-increments. But NO... They want a consecutive list for each of the 12 types of receipts. I'm just waiting to laugh my tail off at them the first time they cross up this sub-number in their record keeping but I've not got the authority to deny the request.