Insert Record based on flag

I have a table say tb_load_files. It contains fields file_name, file_date,file_loc,file_status

Now, File_status cane be x or y. The requirement is there can be multiple records for x status for combination of (file_name, file_date,file_loc) but only one record for y status.

e.g.

file_name  file_date     file_loc  status
abc.txt     12-oct-07     NY         X
abc.txt     12-oct-07     NY         X
abc.txt     12-oct-07     NY         Y

abc.txt     12-oct开发者_Go百科-07     NY         Y    --  NOT ALLOWED

what could be the best way of designing these table?

  a. selecting data before insert

  b. trigger to check if value exists

or any other. Please advice

If there is going to be thousands of entries, I'd suggest that instead of wasting space by using multiple rows with the same data, add a column COUNT. The data can be then unique and can be better optimized with indexes if necessary.

Then, either create PL/SQL procedure callable by client, or (if client expects to work with table) create a updatable view + INSTEAD OF INSERT/UPDATE/DELETE triggers. Code inside it will just increase COUNT on existing record when status is X, or throw error if status is Y.