mysql - Updating same table in stored procedure after UPDATE and INSERT -

relationship : students (1 can have n) addresses

scenario: students can have many records 1 associated record must have 'current' field set 'yes' (other value null) query below should return 1 record per student.

select * address student_id = 5 , current = 'yes' 

problem: people mark more 1 record 'yes' after insert or update same student need avoid it. best way of doing using triggers or stored procedures within mysql?

if update happens on 'address' table should run somewhere mark other records null: update addresses set current = null student_id = in_student_id

if insert happens on 'address' table should run somewhere mark other records null: update addresses set current = null student_id = in_student_id , id <> in_inserted_id

thanks in advance

if need updated automatically after data modified, right approach trigger. notice trigger may call stored procedure.

however not able implement described behaviour in trigger because:

a stored function or trigger cannot modify table being used (for reading or writing) statement invoked function or trigger.

in fact, information "address x current address" should stored in column in students table, foreign key address table. therefore, unicity guaranteed.

something (fiddle here):

create table student (   id int not null primary key,   current_address int,   name varchar(20) );  create table address (   id int not null primary key,   student_id int not null,   contents varchar(50) not null,   constraint student_fk foreign key student_fk_idx (student_id)     references student(id)   );  alter table student   add constraint curraddr_fk_idx     foreign key curraddr_fk_idx (id, current_address)     references address(student_id, id); 

notice structure allows insertion of students no "current address". because @ least 1 2 tables must allow null value foreign key (or else cannot add single row in either table). if makes more sense, let address.student_id null instead, , allow address nobody's address until create corresponding student.