I have a master-detail relationship between two tables, I need to update one field of the master table when all records of the detail table are processed.
For example:
create table master(idmaster serial not null,
status varchar(10) default 'CREATED',
primary key(idmaster));
create table detail(iddetail serial not null,
idmaster integer references master(idmaster),
status varchar(10) default 'REGISTERED',
primary key(iddetail));
Then records of table detail
are filled by a long-lasting process (i.e. a person changes the status of a record when a box is filled with products, then after some time it changes the status of another record and so on after filling all the boxes related to a truck) and I need to know the global status of the process, just by querying the master table.
How can I create an update
query on the master table to change the status when ALL the elements of the detail table contain the value 'FILLED'?.