使用带自动增量字段的触发器

Question

So I am currently creating a database for a MMO (Massively mutliplayer online). This database is going to list all of the items (armor, weapon, quests, npcs, e.t.c.) in the game. I currently have it set to where each different category has it's own table within the database. I.e a table for weapons, armor, e.t.c.. The data will be displayed in a table and the name of whatever it is will be a link that will take the person to a detailed page about whatever they clicked on.

For every table I have a ID field, that auto increments so each row has it's own ID. But since I have multiple different tables the ids are going to overlap. This isn't acceptable because of how the links work. I created a 2nd field called "identifier" and the default value is based on each table. So the default value for this column in the armor table is "armor".

I then created a third field called "combined", now here's the hard part! I created a trigger where before data is inserted into the table, the trigger combines the "id" field and the "identifier" field using CONCAT and puts it in the field combined. Thus creating a unique id no matter how many tables I have. But, since the ID field auto increments, the trigger is just putting a 0 where a different number should be. Here is the trigger code:

set new.combined = concat(new.id, new.identifier)   

This is before insert.

I tried doing a after insert but kept getting an error. I've searched through questions here and can't find an answer for my problem. Any help?

Answer 1

A solution to this is to have a separate table for sequences. Your schema then may look like this

CREATE TABLE item_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE weapon
(
  id INT NOT NULL PRIMARY KEY DEFAULT 0, 
  name VARCHAR(32)
);
CREATE TABLE armor
(
  id INT NOT NULL PRIMARY KEY DEFAULT 0, 
  name VARCHAR(32)
);

A trigger for weapon table

DELIMITER $$
CREATE TRIGGER tg_weapon_beforeinsert 
BEFORE INSERT ON weapon
FOR EACH ROW
BEGIN
  INSERT INTO item_seq VALUES(NULL);
  SET NEW.id = LAST_INSERT_ID();
END$$
DELIMITER ;

A trigger for armor table will be the same

DELIMITER $$
CREATE TRIGGER tg_armor_beforeinsert 
BEFORE INSERT ON armor
FOR EACH ROW
BEGIN
  INSERT INTO item_seq VALUES(NULL);
  SET NEW.id = LAST_INSERT_ID();
END$$
DELIMITER ;

Now if you insert some data into both tables

INSERT INTO weapon(name) VALUES('weapon1');
INSERT INTO armor(name) VALUES('armor1');
INSERT INTO weapon(name) VALUES('weapon2');
INSERT INTO armor(name) VALUES('armor2');

Content of weapon table

| ID |    NAME |
----------------
|  1 | weapon1 |
|  3 | weapon2 |

Content of armor table

| ID |   NAME |
---------------
|  2 | armor1 |
|  4 | armor2 |

Here is SQLFiddle demo