0

OK I have 3 tables:

client:

ClientID (int)auto-inc

clientName (varchar)

tnt_group:

groupID (int)auto-inc

ClientID (int)foreign-key

tnt_item:

itemID (int)auto-inc

itemNo (int)

groupID (int)foreign-key

Now, I need to generate an itemNo for each record in tnt_item, starting from 1 for each ClientID so each client see's their item count starting from 1. I have tried adding a trigger as follows:

BEGIN
    SET NEW.itemNo = (
        SELECT IF(t.itemNo IS NULL, 1, IFNULL(MAX(t.itemNo), 0) + 1)
        FROM (
            SELECT IFNULL(i.itemNo, 0) AS itemNo, g.groupID, g.ClientID, cg.ClientID AS cgClient, cg.groupID  AS cgGroup
            FROM tnt_group cg
            INNER JOIN tnt_group g ON cg.ClientID = g.ClientID
            INNER JOIN tnt_item i ON i.groupID = g.groupID
        ) AS t WHERE t.cgGroup = NEW.groupID
        GROUP BY cgClient
    );
END

This is working, in the case where a client already has at least 1 asset, it gets the max itemNo then adds one. where it is failing, is if a client does not have any items yet, no matter what I do, it sets the first itemNo as NULL

Any tips on what i am doing wrong?

2
  • holes in number allowed, items are only ever soft deleted anyway (deleted column)
    – mike16889
    Commented Sep 27, 2019 at 8:20
  • I would rather have a stored Id, not rely on an records not being deleted or a function that could change over time.
    – mike16889
    Commented Sep 27, 2019 at 8:33

1 Answer 1

1
CREATE TRIGGER tr
BEFORE INSERT
ON tnt_item
FOR EACH ROW
SET NEW.itemNo = 1 + COALESCE((SELECT MAX(t1.itemNo)
                               FROM tnt_item t1, tnt_group t2, tnt_group t3
                               WHERE t1.groupID = t2.groupID
                                 AND t2.ClientID = t3.ClientID
                                 AND t3.groupID = NEW.groupID), 0);

fiddle

1
  • That looks like it will do what I'm after. I can't try yet, will be back at my desk in a couple of hours and will try then. Thanks for your time.
    – mike16889
    Commented Sep 27, 2019 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.