Reputation: 23
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.
Upvotes: 2
Views: 2530
Reputation: 68
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
Upvotes: 2