I trying to add the suffixes to the older (existing) value(s) in the table.
Hence I would have this result:
And if I need to insert dude again, the trigger will check if it exists, then append the next available (-3 in this case) to the existing one, and insert the newest dude WITHOUT a suffix:
Is this possible to do that ?
Thanks in advance.
A trigger in MySQL can't act on mysql table which caused it to fire, hence your plan will not work, at least not as you described it. What you are asking for is going to be very difficult, if not impossible, to do, and would probably end up being a maintenance nightmare. I actually don't see any point to labelling the slug values with versions. I can offer a workaround, namely just building the version suffix at the time you query. This is fairly painless, and frees you from having a draconian table design. Something like this:
SET @rank = 0;
SET @slug = NULL;
CASE WHEN t2.id IS NULL
THEN CONCAT(t1.slug, '-', CAST(t1.label AS CHAR(50)))
ELSE t1.slug END AS slug
@rank:=CASE WHEN @slug = slug THEN @rank + 1 ELSE 1 END AS label,
@slug:=slug AS slug
ORDER BY slug, id
SELECT slug, MAX(id) AS id
GROUP BY slug
ON t1.id = t2.id AND
t1.slug = t2.slug;