SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)

    Trigger to work with an obnoxious request.

    I have this trigger that isn't working...
    Code sql:
    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

    Code:
    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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •