A user photo table with the following fields:

username
path
caption
status

where the status field indicates where the photo is the main one or not. Each user only can have one main photo.

For the deletion operation, another, if there is one, photo's status needs to be changed to main if the removing photo is the main one. What is the best approache to carry out this task: SQL and function/trigger?

Similar situation occur when a user want to change a non-main photo to become a main one.

Any advice?

Thanks,

v.

While a trigger could be made to do this, it would not be trivial - would have to deal with the mutating table problem for one thing.

I would prefer to hide the logic in a packaged procedure, and force the user (i.e. the application) to delete via the procedure rather than an update or delete statement.

Hi, Tony,

Thanks for your response and suggestion.

My thought on the issue is that it is somehow like a DB table constraint and not a business logic. Therefore, it shoud be resolved in the DB layer.

I can implement your suggestion to pass the information what the application knows about whether the photo is a main one or not. I think this solution is a suitable one.

Thanks again.

v.

Here is a solution I just come out.

For the deletion operation, if the deleting photo placement is larger than 1, not the first/main one, only execute the deletion statement.

Otherwise, after the deletion statement, run the following query:

UPDATE photo SET main = 'true' WHERE userid = 'xxx' AND path IN (SELECT path FROM photo WHERE userid = 'xxx' )

This solution basically is on the DB side with a little help from the application (logic).

My feeling of the above query is the subquery can be in a better form, but can't think out one at this moment.

v.