SQL trigger for add some data

Question

Thank you for your help.

I have 3 tables on MySQL. One Table for my users and second is for my user features, the third one is for roles. First of all, I need to add registration on a measures table when the user is inserted. I mean if I add a new user to tbl_users the trigger will grab the user id and write on the tbl_measures that's it. I need just this.

INSERT INTO tbl_measures (user_id) VALUES (tbl_users.id) 

this my triggers it is working partly. when I add a new user trigger add a new line to tbl_measures without id.

Answer 1

When you refer NEW.id in your insert trigger, there are two main cases. In the first case you get the proper id value. This is what you expect. In the second case it's null. This is what happens.

Essential is whether the trigger runs before or after the insert. Before the insert the id does not exist, because the record was not created yet. After the insert the id exists. Long story short code:

DELIMITER //

CREATE TRIGGER PROOFOFCONCEPT
AFTER INSERT
ON EACH ROW
BEGIN

    INSERT INTO tbl_measures(user_id)
    VALUES (NEW.id);

END; //

DELIMITER ;