CODEWITHSUNDEEP
mysqltriggers
Çağlar Can Sarıkaya
Çağlar Can Sarıkaya

Reputation: 135

SQL trigger for add some data

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.

tables shema

Upvotes: 3

Views: 539

Answers (2)

Çağlar Can Sarıkaya
Çağlar Can Sarıkaya

Reputation: 135

I found the solution:

CREATE TRIGGER `after_members_insert` AFTER INSERT ON `tbl_users`
 FOR EACH ROW BEGIN
        INSERT INTO tbl_measures(user_id)
        VALUES(new.id);
END

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76874

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 ;

Upvotes: 1

Related Questions