0

I have two tables, User table and a points table. I designed these tables to be in one-to-one relation, as points contains a foreign key to the user table. as new users get created, I use query INSERT INTO to insert rows into user table. The table creation statements look like below:

CREATE TABLE if not exists User (
    id bigint AUTO_INCREMENT,
    country VARCHAR(128),
    PRIMARY KEY (id)
);

CREATE TABLE if not exists points (
    userid bigint UNIQUE NOT NULL,
    points bigint
);

ALTER TABLE points ADD CONSTRAINT fk_user_id FOREIGN KEY (userid) REFERENCES `User` (id);

Now each time a user is created and inserted into the User table, I want to trigger; so that the same user gets inserted into the points table. I tried this query below and I know it is wrong, I want to take the id (PK) of the user inserted and insert that into points table as well. So each user will have an instance on the other table too. points attribute can be NULL. How can I correct this query ?

CREATE TRIGGER _afterUserCreate BEFORE INSERT ON USER FOR EACH ROW
BEGIN 
     INSERT INTO points (userid, points)
        id AS userid,
        NULL AS points
END

I want the id to be the newly auto incremented userid from the User table.

1 Answer 1

2
CREATE TRIGGER _afterUserCreate AFTER INSERT ON User FOR EACH ROW
BEGIN
     INSERT INTO points (userid) VALUES (NEW.id);
END

It's important that this trigger is AFTER INSERT because the NEW.id is not assigned the auto-increment value yet at the time a BEFORE INSERT trigger is executed.

3
  • I am a bit new, How can I convert this into 1 line sql statement, as I enter this to the terminal I get syntax error and can't find ; to end the statement.
    – umarkaa
    Apr 3 at 20:42
  • @umarkaa I get syntax error Of course, you must redefine DELIMITER (now server cannot determine does a delimiter finalizes your trigger creation code or it is a part of the trigger code). But the trigger contains only 1 statement - so simply remove BEGIN and END keywords.
    – Akina
    Apr 4 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.