Post reply

Need to prevent row-level updates and deletes

  • December 28, 2006 at 1:11 pm

    #170801

    Hi All,

    I haven't personally tried or seen this done before, but is this something I can do with a database trigger?  If so, I would need to only have it "in effect" only on rows where a "processed" flag field is "on".  Essentially I want to allow modifications and deletions to a table rows acting as a journal until I mark the row as "processed."  Once processed, no modifications to that row should be allowed.

    Is a trigger the right way to go here?  Any other ideas/suggestions?

     

    Thanks!

    -Matt

  • December 28, 2006 at 3:35 pm

    #680388

    I don't think anything other than trigger in scenario...

    In your trigger you can check the flag and rollback the transaction if the flag is ON and raise the error to notify the users...

     

    MohammedU
    Microsoft SQL Server MVP

  • December 28, 2006 at 11:32 pm

    #680454

    Triggers is certainly an option, and probably is the one with the least work required.

    Just remember with triggers that a trigger fires once for an change and has all rows for the change in the inserted/deleted tables. What would you do if a user issued a multi-row update that affected both rows that are procesed and ones that aren't?

    The other that I would suggest would be to force all updaes/deletes to the table to be done through stored procedures. then you could check the flag and only do the update/delete if allowed

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply