mysql - after insert trigger to insert data into other table

Question

I have two tables - 

League        (lg_id, lg_Userid, lg_JoinDate)

Here, lg_id is PK and its auto incremented.

LeagueMember  (lgMbr_lg_id, lgMbr_Userid, lgMbr_JoinDate)

lgMbr_lb_id is a FK referencing League(lg_id)

After a row is inserted into League table, a corresponding row should be inserted into leagueMember with the same lg_id which was inserted into League table. I am trying to create an after insert trigger for this.

CREATE TRIGGER trigger_league AFTER INSERT ON `League` 
FOR EACH ROW 
BEGIN 
    INSERT INTO `LeagueMember` (`lgMbr_lg_id`, `lgMbr_Userid`,`lgMbr_JoinDate`) values (NEW.lg_id,NEW.lg_UserId,'2000-10-10')
END;

I tried this but it doesn't work. Whats wrong here?

Edit -

I receive an error message which says there's an error with my syntax. Based on this mysql create trigger reference page I even tried this, but I still get a syntax error.

CREATE TRIGGER trigger_league AFTER INSERT ON `League` 
FOR EACH ROW 
BEGIN 
    INSERT INTO `LeagueMember` SET lgMbr_lg_id = NEW.lg_id;    
END;

it won't?? why? After row is inserted to a table. I pick that row's PK value and insert into a referencing table. Shouldn't that work? — pavanred
– pavanred, Commented Nov 21, 2010 at 20:06
how many times do you create a league ? - see my answer which i just knocked up for you. — Jon Black
– Jon Black, Commented Nov 21, 2010 at 20:12

Jon Black · Accepted Answer · 2010-11-21 20:13:54Z

3

maybe this might help ???

drop table if exists league;
create table league
(
league_id smallint unsigned not null auto_increment primary key,
user_id int unsigned not null,
name varchar(255) unique not null
)
engine=innodb;

drop table if exists league_user;
create table league_user
(
league_id smallint unsigned not null,
user_id int unsigned not null,
created_date datetime not null,
creator_flag tinyint unsigned not null default 0,
primary key (league_id, user_id)
)
engine=innodb;


drop procedure if exists create_league;
delimiter #

create procedure create_league
(
in p_user_id int unsigned,
in p_name varchar(255)
)
begin

declare v_league_id smallint unsigned default 0;

    insert into league (user_id, name) values (p_user_id, p_name);

    set v_league_id = last_insert_id();

    call add_league_user(v_league_id, p_user_id, 1); -- 1 = owner
end #

delimiter ;

drop procedure if exists add_league_user;
delimiter #

create procedure add_league_user
(
in p_league_id smallint unsigned,
in p_user_id int unsigned,
in p_creator_flag tinyint unsigned
)
begin
    insert into league_user (league_id, user_id, created_date, creator_flag) values 
        (p_league_id, p_user_id, now(), p_creator_flag);
end #

delimiter ;

call create_league(1,'Premiere League');

call add_league_user(1,2,0);
call add_league_user(1,3,0);
call add_league_user(1,4,0);

select * from league;
select * from league_user;

answered Nov 21, 2010 at 20:13

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pavanred Over a year ago

Thanks. My first time with php and mysql. I wasn't aware of last_insert_id(). I wouldn't bother creating triggers if I can do the same with sp.

Jon Black Over a year ago

You're welcome - i generally avoid trigs as they have a habit of being forgotten about. Keep that code in sprocs and way to go :)

Collectives™ on Stack Overflow

mysql - after insert trigger to insert data into other table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related