Update MySQL table after row is inserted when field value is null

Question

I have a MySQL table that has an auto incrementing primary call and a UUID. Both serve as unique IDs, as there are two tables with similar objects and there needs to be a unique identifier across both tables (and possible more objects still).

So table looks:

table A
_______
ID int pk, ai
UUID varchar, uq
other columns....

The application will insert into this table, but chances are that it won't always have a UUID generated. Sometimes the application provides the UUID, sometimes I need MySQL to create one.

How can that be achieved? - I have tried with this trigger:

CREATE TRIGGER trig_ains_aw_trckid 
    AFTER INSERT ON table_a 
    FOR EACH ROW
    begin 
        update table_a set UUID = (SELECT concat('k_', uuid())) where UUID is null;   
    end

Now I cannot insert into the table, as the trigger seems to be sitting in front of it:

Can't update table 'account_keywords' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any help would be most appreciated and any explanation as to what I'm doing wrong even more so. Without stating the obvious, the UUID should obviously stay the same... Having not needed to use Triggers before, I think I do not know much about them. In MS SQL this was actually quite easy!

Thanks,

Chris

Is not the update statement processing against every row not just the inserted one? so if there are two inserted records the system is looping through twice and blocking itself.. I believe you need to use the NEW keyword and link back to ID field to limit the update to only the "NEW" Records? — xQbert, Dec 15 '14 at 18:27
I did try that as xQbert suggested. I get the error message: Can't update table 'table_a' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. — dengar81, Dec 15 '14 at 19:57

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

Extend this answer for non null cases:

CREATE TRIGGER trig_ains_aw_trckid
  BEFORE INSERT ON table_a 
  FOR EACH ROW
  SET UUID = uuid() where UUID is null;

edited May 23 '17 at 11:50

Community

1
1

answered Dec 15 '14 at 18:31

TJ-

14,085
12
59
90

same error message as below. Also your statement reads "before". I have seen that elsewhere, but "before" doesn't seem to make sense, because how can an ID be added before the records gets created? – dengar81 Dec 15 '14 at 19:53

score 0 · Answer 2 · answered Dec 15 '14 at 18:35

0

What about:

CREATE TRIGGER trig_ains_aw_trckid 
    AFTER INSERT ON table_a 
    FOR EACH ROW
    begin 
        update table_a set UUID = (SELECT concat('k_', uuid())) 
        where UUID is null 
              and ID = NEW.ID   
    end

Otherwise isn't the for each row infact updating all null records, which if there's more than 1 being processed could cause the system to see it needs to lock a single record multiple times and the system is unable to determine which new.ID's update should work. By limiting to the specific record, I believe you can eliminate your error.

answered Dec 15 '14 at 18:35

xQbert

34,733
2
41
62

I keep getting the error message: Can't update table 'table_a' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. – dengar81 Dec 15 '14 at 19:52
Perhaps change it to a before update and use a case statement around UUID at that point. – xQbert Dec 15 '14 at 20:12
After googling this matter for a while longer, it seems that MySQL can't run a trigger on the table itself. If someone could confirm? – dengar81 Dec 15 '14 at 21:07

Update MySQL table after row is inserted when field value is null

2 Answers2