Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: To update record w/o changing date/time value

    Hi

    I want to update records in a table without affecting the datetime value.

    But whenever I try to use the Update query the datetime field gets updated too.

    Here:

    Table name: StdM

    Columns:
    StudID
    StudName
    Marks
    DateofJoin

    My Query:
    Update StdM SET Marks=65 WHERE StudID=755

    After executing the above query the datetime field i.e "DateofJoin" also gets updated to current date & time of the system when the query is executed.

    My question: Is it possible to use Update command to update only marks without affecting the DateofJoin field value?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    There must be a trigger defined on that table that will automatically update that datetime column. Here you can find a solution to (temporary) disable triggers.

    But make sure you know the implications of circumventing that trigger. It must have been created for a reason. Make sure you have the right to circumvent it, unless of course, you want to be free to do activities on the database of a stealthier nature, without leaving any traces...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2010
    Posts
    3
    Thanks a lot Wim...

    Yes there is a trigger set, whenever a record is modified the datetime field value gets updated to current date & time.

    Yes by disabling the trigger I get what I wanted.

    Is there any other way i.e. without disabling the trigger... override it (trigger) so that the datetime field doesn't get updated?

    something like..

    Update StdM SET Marks=65, DateofJoin=(Select or DATEADD...) WHERE StudID=755


    ??

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    There is another solution:
    1. create a second trigger, that updates the DateOfJoin value to that from inserted table
    2. use sp_settriggerorder SP a set this trigger as 'Last'
    3. alter both triggers as presented here: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Almost sounds like you're trying to circumvent business rules and/or security reporting capabilities - usually not a good idea if you want to keep your job. Having said that, determine the value of such a trigger with the database owner and if it's no longer needed, drop it.

    Actually, because of student ID and student name - this smells of homework. If not, updating a student's marks for a class should be verboten unless being performed by the instructor - IMO.
    -- You can't be late until you show up.

  6. #6
    Join Date
    Oct 2010
    Posts
    3
    Thanks aflorin27 & tosscrosby!

    Well because wrong column i.e. 'DateofJoin' value was getting updated after executing an update command that's why I was asking.

    Now I have added new field 'Updated_On' to remove such misconception...

    Anyways

    Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by aflorin27 View Post
    There is another solution:
    1. create a second trigger, that updates the DateOfJoin value to that from inserted table
    2. use sp_settriggerorder SP a set this trigger as 'Last'
    3. alter both triggers as presented here: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
    OK, I am trying to decide whether that is "Genious", or "Evil Genious".
    I'm leaning towards the second option, but kudos for creativity just the same. I can't recommend putting this kind of solution into any sort of production environment.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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