SQL & PL/SQL

Hi!

I want to implement a simple boolean logic where the value of a char column named 'active' can have a value of 'Y' in only one row. When the value is set to 'Y' for this row, the row which had that column set to 'Y' previously should be set to 'N'.

I solved this with two triggers. Since I can't update the column in an for each row trigger, I introduced another column 'actived_just_set' that I set to 'Y' in the for each row trigger and then in a statement trigger I set all rows with active='Y' and active_just_set<>'Y' to active='N' and then I clean up and set active_just_set='N' for all rows.

This works, but seems clumsy. I don't like to have an extra column for data integrity also.

Thanks for your help,
Marcus