MySQL - Update a column after information get's inserted

Question

I have a table in MySql table like the following

row_timestamp timestamp
, row_id int(11) auto_increment
, mrn char(17)
, patients_last_name varchar(50)
, patients_first_name varchar(50)
, ssn char(4) default '0000'
, visit_key NULL

upon the insertion of a record, I'd like visit_key to bet set to visit_key = concat(mrn, row_id) I was trying to accomplish this with a before insert trigger to no avail, I kept getting that the mrn column was not in the field select list.

Update

I tried the following, which seems not to work because the auto_increment has not yet incremented:

set new.visit_key = concat(new.mrn, new.row_id)

I also tried

set new.visit_key = concat(new.mrn, max(row_id)+1)

I am thinking of the trigger to sort of act like a calculated field in MS Access, is this reasonable? Thoughts? Would it not be possible to do since the visit_key would technically be NULL and you cannot update a new value?

UPDATE

I used the following code that I adapted from this question here

DELIMITER //
CREATE TRIGGER vkt AFTER INSERT ON demographic_information
FOR EACH ROW
BEGIN
  UPDATE `demographic_information` SET visit_key_test = concat(new.mrn, mew.row_id) WHERE row_id = NEW.row_id;
END;
//
DELIMITER ;

and got the following error message:

INSERT INTO  `manchuco_nys_trauma`.`demographic_information` (

`row_timestamp` ,
`row_id` ,
`mrn` ,
`patients_last_name` ,
`patients_first_name` ,
`ssn` ,
`visit_id` ,
`visit_key_test`
)
VALUES (

CURRENT_TIMESTAMP , NULL ,  '123456',  'Sanderson',  'Steven',  '1234',  '12345670', NULL
)
MySQL said: Documentation

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

Thank you,

Edit your question and provide the code for your trigger. – Gordon Linoff Mar 17 '16 at 01:22 — Gordon Linoff, Mar 17 '16 at 01:22

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

0

Try selecting the auto increment value inside the trigger:

CREATE TRIGGER trigger_name AFTER INSERT ON yourTable FOR EACH ROW
BEGIN
    DECLARE next_id integer;
    SET next_id = (SELECT AUTO_INCREMENT
                   FROM information_schema.TABLES
                   WHERE TABLE_SCHEMA='yourDB' AND TABLE_NAME='yourTable');
    SET new.visit_key = CONCAT(new.mrn, next_id);
END

I discovered this approach in this SO article.

edited May 23 '17 at 12:23

Community

1
1

answered Mar 17 '16 at 01:33

Tim Biegeleisen

502,043
27
286
360

this is not working I get an error checking syntax for my version of MySQL, seems there is an issue with the DECLARE next_id INT; – MCP_infiltrator Mar 17 '16 at 21:05
I will get it tonight – MCP_infiltrator Mar 21 '16 at 11:56

score 0 · Answer 2 · answered Mar 17 '16 at 01:33

0

What you observe seems normal: since you're using BEFORE INSERT the id value doen't exist yet.
And the same applies for your try with concat(new.mrn, **new**.row_id): NEW has no sense at the moment.

So I suggest you use AFTER INSERT instead.

answered Mar 17 '16 at 01:33

cFreed

4,404
1
23
33

I tried doing after insert as well and I got a message that I cannot do that. – MCP_infiltrator Mar 17 '16 at 01:41
@MCP_infiltrator What exactly was the message? – cFreed Mar 17 '16 at 01:43
I'll screenshot it when I get home, logging into my cpanel has been labeled verboten by the network... – MCP_infiltrator Mar 17 '16 at 12:07
updating of NEW record is not allowed in AFTER INSERT – MCP_infiltrator Mar 17 '16 at 20:58
@MCP_infiltrator Hmm... Seems to mean that you've used `concat(new.mrn, new.row_id)`: try change it for `concat(old.mrn, old.row_id)`. And BTW, use `UPDATE SET...` in your AFTER INSERT – cFreed Mar 17 '16 at 21:52

MySQL - Update a column after information get's inserted

2 Answers2