Trigger to work with an obnoxious request

Databases
1

I have this trigger that isn’t working…


CREATE TRIGGER receipt_serial_create AFTER INSERT ON receipt
FOR EACH
ROW BEGIN
UPDATE receipt r SET r.serial = ( SELECT MAX( serial )
FROM receipt
WHERE `type` = NEW.`type` ) +1
WHERE receipt_id = NEW.receipt_id;

END //

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.

2

It’s 4AM here so I’m not thinking straight enough to try to fix the trigger, but the thought occurred that you can avoid a trigger altogether with an INSERT INTO…SELECT query. The SELECT are just the hardcoded values you want to insert other than the MAX(serial)+1 part.

3

you of course can create a column for receipt type and an auto increment column and you can make the primary key both columns. it necessitates the key being across both columns.

so you will get an incrementing column that changes for each receipt type

receipt_type auto_inc_column
paid 1
unpaid 1
new 1
paid 2
sold 1
paid 3

etc.
Would this serve you any better?

note you can only use this on a myisam table type though. thought i’d bring it up in case it helped solve a problem in an easier way.