Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Unanswered: Trigger after Column Update

    I have a table called Projects, within that table is a field called UserId

    I want to log everytime that UserID field in changed in a log called ProjectAllocationLog

    so far i have written

    CREATE TRIGGER LogUser
    ON Projects
    FOR Insert, Update
    AS
    if Update(UserId)
    begin

    INSERT INTO "ProjectAllocationLog" ("projectid", "UserID", "createdate")
    VALUES ("123", "123", Now())

    End


    What I am stuck with is how do I pass the values of the field into the Values

    ie where I have put "123", I actually want to pass the value of ProjectID, and UserID from the projects row that is changing

    can anyone help me please

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I raised a verrrrry similar question just 1 hour ago!
    The response I recieved from blindman is applicable to your problem here.

    http://www.dbforums.com/showthread.php?t=1623580
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The Update([column]) function can be misleading. It will tell you whether a column has been updated, but will not tell you whether the value was actually changed. So, if you issue an update against a table then all the columns in that statement are considered "updated", even if their value did not actually change. Considering that may applications write an entire record to a database rather than issuing an updated only for the modified columns, using the Update() function can lead to a lot of false hits.
    A more accurate method is to compare the values from the INSERTED and DELETED tables to see whether your column value actually changed.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Oct 2007
    Posts
    30
    where can i find more information on these inserted and delected tables please?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I simply used Google...
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Delected? Mmm....yummy tables!

    Try Books Online first.
    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
  •