MySQL Create Trigger Insert Content into another table from the same database

Question

I want to create a Trigger which create a new table Row. I'm completely new in the "Trigger Language" and i need your Help.

My Database look like the following.

user
----------------------------------------------------
| userid | username | password | full_name | email |
----------------------------------------------------
| 67     | testuser | xxxxxxxx | thefullna | email |
----------------------------------------------------
| 68     | testuse2 | xxxxxxxx | thefulln2 | email |
----------------------------------------------------
| 69     | testuse3 | xxxxxxxx | thefulln3 | email |
----------------------------------------------------

highscore_easy
------------------------------------------------------
| ID   | user     | time     | date | level | userid |
------------------------------------------------------
| 200  | testuser | 11113233 | date | 444   | 0      |
------------------------------------------------------
| 201  | testuse2 | 11113235 | date | 444   | 0      |
------------------------------------------------------
| 203  | testuse3 | 11113236 | date | 444   | 0      |
------------------------------------------------------

If a new User gets created a new Row will be created in the Table "user". When the new row gets created i want to create a Trigger that a "DEFAULT" Row get created in "highscore_easy".

The Default Row have to take the username that is filled in in the Table "user" the time, date and level can be "0" in the default row and the USERID in "highscore_easy" have to be the same userid as in the Table "user".

So if i create a User now:

user
----------------------------------------------------
| userid | username | password | full_name | email |
----------------------------------------------------
| 88     | example  | xxxxxxxx | thefullna | email |
----------------------------------------------------

highscore_easy
------------------------------------------------------
| ID   | user     | time     | date | level | userid |
------------------------------------------------------
| 200  | example  | 0        | 0    | 0     | 88     |
------------------------------------------------------

ID will be created on its own in highscore_easy.

Sorry for my bad English if i did some mistakes. It would be awesome if you could help me.

Maybe there is a way to solve this without a Trigger,but i would not know another solution.

Piotr Gaczkowski · Accepted Answer · 2013-12-07 15:56:40Z

2

You may try something like this:

DELIMITER $$
CREATE TRIGGER `ai_user` AFTER INSERT ON user
  FOR EACH ROW BEGIN
  INSERT INTO highscore_easy 
  SET 
    user   = NEW.username, 
    userid = NEW.userid, 
    time   = 0, 
    date   = 0, 
    level  = 0;
END

In single trigger you may have as many insert as you need:

DELIMITER $$
CREATE TRIGGER `ai_user` AFTER INSERT ON user
  FOR EACH ROW BEGIN

  INSERT INTO highscore_easy 
  SET 
    user   = NEW.username, 
    userid = NEW.userid, 
    time   = 0, 
    date   = 0, 
    level  = 0;

  INSERT INTO highscore_expert
  SET 
    user   = NEW.username, 
    userid = NEW.userid, 
    time   = 0, 
    date   = 0, 
    level  = 0;

END

edited Dec 7, 2013 at 15:56

answered Dec 7, 2013 at 14:29

Piotr Gaczkowski

685 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christian S Over a year ago

Hey, thanks for your answer. I just cant execute the SQL Command it shows me that: pastebin.de/37813

Christian S Over a year ago

Is it possible to add more than one Table to the Trigger? Like -> INSTER INTO highscores_easy, highscores_normal, highscores_expert SET

Piotr Gaczkowski Over a year ago

If you execute this query from command line remeber to add $$ after last line since whe change delimiter to $$ :) Like this should be fine: pastebin.de/37815

Piotr Gaczkowski Over a year ago

Yes, you could have more than one insert inside trigger but as separate queries since MySQL doesn't support syntax like INSERT INTO table1,table2,... ect. I've update my response.

Collectives™ on Stack Overflow

MySQL Create Trigger Insert Content into another table from the same database

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related