i have two mysql tables.A java application is working there which picks data and insert into second table.what i want is if there are less than 60 rows in targeted table then java application should stop when there is less than 60 entries only then it should insert data in that table.currently i am using simple insert without any condition.

6 Years
Discussion Span
Last Post by smantscheff

You can solve your problem on the application level using a query which checks for row count in table B and only inserts into table A if there are less than 60 rows in B.
You can also have half a solution with a trigger which counts the records in B and provokes an error if there are too many. Like this:

drop table if exists a;
drop table if exists b;
create table a (id integer auto_increment primary key);
create table b (id integer auto_increment primary key);
drop trigger a_before_insert;
delimiter //
create trigger a_before_insert before insert on a
for each row
	declare i integer;
	select count(*) from b into i;
	if i > 3 then 
		set new.id = 1;
	end if;
delimiter ;
insert into b values (1),(2),(3);
insert into a values (1);
insert into b values (4);
insert into a values (2);
>> ERROR 1062 : Duplicate entry '1' for key 'PRIMARY'

This way you can avoid the insertion of too many records on the database level, but it is a very dirty solution as it manipulates data and has certain data prerequisites and provokes an error. Maybe someone has a better idea?

This article has been dead for over six months. Start a new discussion instead.
Be sure to adhere to our posting rules.