Professional Web Applications Themes

SQL Server Update Trigger - Microsoft SQL / MS SQL Server

I am trying to write a trigger that is called on row update. The problem is that I need to compare the old value of a certain column with the new value. Is this possible? I thought that if I selected from the table and compared the value with "inserted" I could accomplish what I want, but it seems that I am getting the same value for both statements. Thanks, Nate SELECT YTD_DuesCalcFromCurrent = YTD_DuesCalc FROM tbl_Statements WHERE (Statement_ID = Statement_ID) SELECT YTD_DuesCalcFromInserted = YTD_DuesCalc FROM inserted...

  1. #1

    Default SQL Server Update Trigger

    I am trying to write a trigger that is called on row update. The problem is
    that I need to compare the old value of a certain column with the new value.
    Is this possible? I thought that if I selected from the table and compared
    the value with "inserted" I could accomplish what I want, but it seems that
    I am getting the same value for both statements.

    Thanks,
    Nate

    SELECT YTD_DuesCalcFromCurrent = YTD_DuesCalc FROM tbl_Statements WHERE
    (Statement_ID = Statement_ID)
    SELECT YTD_DuesCalcFromInserted = YTD_DuesCalc FROM inserted


    Nathan Guest

  2. #2

    Default Re: SQL Server Update Trigger

    You can find the "old" value by looking at the 'deleted' virtual table.

    Keep in mind that triggers are fired once for each update/insert/delete
    _statement_, not once per row. From your use of variable I assume that you
    haven't realised that yet (I don't mean to be patronizing here, I just know
    that it's something that causes problems for quite a few people), so maybe
    it is a good idea to post create scripts for your table, your current
    trigger, some sample data, and the results you like to achive so someone can
    provide you with an example of how to implement your trigger.

    hth



    "Nathan A. Zobrist" <com> wrote in message
    news:phx.gbl... 
    is 
    value. 
    compared 
    that 


    Jacco Guest

  3. #3

    Default Re: SQL Server Update Trigger

    Thanks for your help! Yes, I did know that the triggers are fired once per
    statement, not per row... I just needed to check for a certain condition.
    The 'deleted' keyword was exactly what I was looking for.

    Thanks again,
    Nate


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    know 
    can [/ref]
    problem 
    > value. 
    > compared 
    > that 
    >
    >[/ref]


    Nathan Guest

Similar Threads

  1. SQL Server Trigger question
    By ego-adam in forum Coldfusion Database Access
    Replies: 3
    Last Post: October 14th, 01:46 AM
  2. Allowing update of column only from trigger
    By Shawn Harrison in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 31st, 06:59 PM
  3. Before update or delete trigger to insert ?
    By Cindy Gold in forum IBM DB2
    Replies: 2
    Last Post: August 12th, 01:38 PM
  4. Update trigger problem
    By Stephen F Zelonis in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 01:52 PM
  5. Trigger to update another data source
    By Mandar Naik[MSFT] in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 09:20 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139