Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Unanswered: Need some helps on this task

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Need some helps on this task

    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.

  3. #3
    Join Date
    Oct 2002
    Posts
    37
    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.

  4. #4
    Join Date
    Oct 2002
    Posts
    37
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •