CODEWITHSUNDEEP
mysqldatabasetriggers
Abdel Raoof Olakara
Abdel Raoof Olakara

Reputation: 19353

Read and write autoincrement values from trigger

I am having trouble with mysql triggers assigning primary key values. Consider my tables:

v_object : v_object_id,create_date, .. some generic attributes
users : user_id,name, .. attributes of a user

I have a trigger on users table as follows:

create trigger new_vobject_for_user before insert on users
for each row begin
    insert into v_object(v_type,...) values(...);
    set NEW.id = // How do i get the new v_object's id here?
end;

When I insert an user, I need the vobject created and use its new auto incremented value for user_id.

I also would like to know if there is a way to insert the users without specifying the id. For example:

insert into users(n1,n2,n3,...) values(m1,m2,m3,...);

It gives me an error saying user_id is not specified. And that's true.. what I am trying to do is create vobject & generate id in the trigger.

Upvotes: 1

Views: 3408

Answers (2)

rabudde
rabudde

Reputation: 7722

use LAST_INSERT_ID() 

create trigger new_vobject_for_user before insert on users
for each row begin
    insert into v_object(v_type,...) values(...);
    set NEW.id = LAST_INSERT_ID();
end;

alternatively have a look at INFORMATION_SCHEMA tables and get last auto increment for table v_object

create trigger new_vobject_for_user before insert on users
for each row begin
    insert into v_object(v_type,...) values(...);
    set NEW.id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'v_object')
end;

Upvotes: 1

ijse
ijse

Reputation: 3035

SELECT @Result:=LAST_INSERT_ID(); to get the last insert id.

or 

SET Result=LAST_INSERT_ID();
SELECT Result;

Upvotes: 1

Related Questions