0

I have the following MySQL table named users

+----------------+----------------+----------+     +----------+
|    uniquenum   |      state     |   type   |     |  custid  |
+----------------+----------------+----------+     +----------+
+----------------+----------------+----------+     +----------+
|     00001      |     03         |    1     |     | 10300001 |
+----------------+----------------+----------+     +----------+
|     00002      |     02         |    3     |     | 30200002 |
+----------------+----------------+----------+     +----------+

The above three columns uniquenum, state and type are all concatenated and shown in the custid column. This I was able to achieve by running the following SQL query:

UPDATE users SET custid = concat (type,state,uniquenum)

What I am trying to achieve is to make the custid column to automatically get the values of the other three columns when new values are added or the old ones updated. So, I tried to create a trigger as follows:

CREATE trigger insert_custid
before insert on users
for each row
set new.custid = concat(new.type,new.state,new.uniquenum);

create trigger update_custid
before update on users
for each row
set new.custid = concat(new.type,new.state,new.uniquenum);

When I do this and insert into the table, the custid column does not store the correct values. Instead it returns all zeros in place of the value of uniquenum. uniquenum is AUTO_INCREMENT with zerofill and starts at 00001 with an interval of 1. Could this be causing an issue? Any help is greatly appreciated. Thank you :)

Ajit KS
  • 171
  • 2
  • 14
  • 1
    auto_inc ids aren't assigned at the time the `before` fires. the value's only available AFTER the insert, so try changing to an `after`, instead of `before`. – Marc B Jan 28 '16 at 20:34
  • @MarcB Tried adding `after` instead of `before`. But, I'm getting an error - `#1362 - Updating of NEW row is not allowed in after trigger`. Any ideas on how to achieve this? – Ajit KS Jan 29 '16 at 14:58
  • target the row/record that was inserted, not the new one. – Marc B Jan 29 '16 at 15:04
  • @MarcB Sorry to bother you again, but I am unable to get this to work. Can you please change my SQL command above and tell me what I need to paste in the MySQL command line. Thank you for your help. – Ajit KS Jan 29 '16 at 15:11
  • http://stackoverflow.com/questions/24404540/using-auto-increment-value-in-mysql-before-insert-trigger – Marc B Jan 29 '16 at 15:13
  • @MarcB Unable to get this to work. Getting errors :( I am still a newbie in MySQL. – Ajit KS Jan 29 '16 at 15:23

0 Answers0