Why is this BEFORE INSERT trigger not working in MySQL?

Question

I've got a MySql function called Base52Encode, which takes in a bigint and returns a varchar(12).

I've got a table called Things that has a bigint auto_increment for the primary key, called ThingId, and a second column of type varchar(12), called ShortCode.

I want to set the value of ShortCode to the base-52 encoded value of the ThingId primary key, which gets created via the auto_increment.

DELIMITER $$

CREATE TRIGGER BeforeInsertThings
BEFORE INSERT ON Things 
FOR EACH ROW

BEGIN

    set new.ShortCode = Base52Encode(new.ThingId);

END $$

But whenever I insert a row, the ShortCode value always gets set to 0 (zero), which means the value being passed into Base52Encode is also a zero.

I'm assuming the issue is that the auto_increment value hasn't kicked in yet when this trigger runs.

So how exactly can I solve this issue?

Material duplicate: http://stackoverflow.com/questions/18400436 — pilcrow, Apr 18 '14 at 18:33

score 1 · Answer 1 · answered Apr 16 '14 at 01:42

1

Try this

DELIMITER $$

CREATE TRIGGER BeforeInsertThings
BEFORE INSERT ON Things 
FOR EACH ROW

BEGIN
    Select ThingId into largestId order by ThingId Desc limit 1;
    Set newThingId = largestId + 1;
    set new.ShortCode = Base52Encode(newThingId);

END $$

answered Apr 16 '14 at 01:42

Ashraf Bashir

9,686
15
57
82

2

Interesting workaround, but isn't there a chance that two insertions could be happening at the same time, resulting in the ShortCode being set to the exact same value (since both insert triggers would see the same largestId)? – Mason G. Zhwiti Apr 16 '14 at 05:20

Alex W · Answer 2 · 2014-04-13T01:25:21.200

0

The reason I'm assuming it isn't working is because when you insert a row, you aren't specifying a primary key because you want it to just auto increment. So, you have no new.ThingId value. You can easily solve this by using AFTER INSERT instead of BEFORE INSERT:

DELIMITER $$

CREATE TRIGGER AfterInsertThings
AFTER INSERT ON Things 
FOR EACH ROW

BEGIN

    set new.ShortCode = Base52Encode(old.ThingId);
    # Or, you could use:
    # set new.ShortCode = Base52Encode(last_insert_id());

END $$

edited Apr 13 '14 at 01:25

answered Apr 13 '14 at 01:18

Alex W

37,233
13
109
109

2

When I try to do this in an AFTER INSERT, it tells me I can't edit the table that is currently being acted on in the trigger (I guess to avoid circular references). – Mason G. Zhwiti Apr 13 '14 at 02:34

score 0 · Accepted Answer · edited May 23 '17 at 12:30

0

As explained in this similar question on SO, what I want to do is impossible within the context of triggers in MySQL, and the only solution is to do what I'm already doing to work around this issue: handle the extra logic that needs to run directly after my existing INSERT code in my stored procedure. Not ideal, but it is what it is.

edited May 23 '17 at 12:30

Community

1
1

answered Apr 21 '14 at 19:40

Mason G. Zhwiti

6,444
11
61
97

You add your own answer copying mine (my link), and you mark it as good answer. This is not fair. You have explained this solution in my question comments. If you start a bounty people giving you the way to the solution expect at least to be recognized. – Cedric Simon Apr 22 '14 at 16:16
@CedricSimon I am fully planning on giving you at least some bounty credit once SO allows it. However, half your "answer" was just incorrectly chastising me for posting, and the other half simply linked to another question on SO, without explicitly answering my question. The reason I marked my followup as an answer is because it wraps everything up for new people coming into this question later. Flat out: what I want to do is impossible in a trigger, and the only solution is to handle the logic externally. If your answer stated that, I would mark it as the answer. – Mason G. Zhwiti Apr 22 '14 at 18:03

score -1 · Answer 4 · edited May 23 '17 at 11:58

-1

This question seems a duplicate of this question.

As per this question, auto increment are set after insert. So do not count on new.field value to retrieve it for you in a before trigger.

You should search better into existing questions before posting question :S.

If you question is not about a new feature, or new software / hardware, there is a high probability it has been asked and answered already, in the more than 6 millions question on this site...

edited May 23 '17 at 11:58

Community

1
1

answered Apr 19 '14 at 01:24

Cedric Simon

4,571
4
40
52

1

Hi @CedricSimon. Thanks for the advice, but I actually always do a ton of searching before I post to SO. As I did not know the issue was related to how auto-increment works in MySQL when I originally posted, how would I have turned up the question you mentioned, which is about auto-increment? – Mason G. Zhwiti Apr 21 '14 at 19:36
As far as I understood, you want to to do a encoding of a value that has not been set yet. Workaround are proposed in questions linked. – Cedric Simon Apr 21 '14 at 19:39
Yeah I read through the links and there seem to be two proposed workarounds. One is basically what @AshrafBashir proposed as an answer in this question, but I'm concerned that there is a race condition there that could be exhibited with multiple records being inserted at once, either by the same or different users. The other workaround is basically what I'm already doing: in my stored proc, do the insert, then immediately do the addition work I was hoping to do in the trigger itself. I'll post an answer making this clear. – Mason G. Zhwiti Apr 21 '14 at 19:44

Why is this BEFORE INSERT trigger not working in MySQL?

4 Answers4