Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16

    Unanswered: Updateing a field using a TRIGGER

    Hi

    I have a table called cur and i want one of the fields to contain a value of two other fields multiplied together

    Table 'cur' Fields ID, A, B, C
    field C must = A*B

    I have this so far and it works but updates ALL of the fields C rows

    ______________________________________________
    ALTER TRIGGER TG_cur
    ON cur
    AFTER INSERT
    AS
    BEGIN
    Update cur
    SET C = (i.A*i.B)
    from inserted i
    _______________________________________________

    RESULT:
    ID|A|B|C
    1 - 3 - 4 -20
    2 - 6 - 6 -20
    3 - 8 - 9 -20
    4 - 2 - 3 -20
    5 - 4 - 5 -20


    I think need a where statement at the bottom but the followings does not work
    where ID = i.ID

    REQUIRED RESULT
    ID|A|B|C
    1 - 3 - 4 -12
    2 - 6 - 6 -36
    3 - 8 - 9 -76
    4 - 2 - 3 -6
    5 - 4 - 5 -20
    any help would be great

    many thanks

    Bil
    Last edited by billy_boi; 02-23-07 at 11:53.

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by billy_boi
    Update cur
    SET CP = (i.A*i.B)
    from inserted i
    You should update Inserted (i), not Cur.

    Also, you really shouldn't store that at all. Just calculate it when you need it.
    Last edited by ivon; 02-23-07 at 11:56.

  3. #3
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    Hi

    inserted i is called to get the values that have just been inserted by the user???

    Inserted i is not a table, cur is the table.

    + i agree in this example it is probable not needed and could be calculated when needed, however this is a cut down of a bigger trigger.


    MISTAKE ON tigger:

    SET CP = (i.A*i.B)
    should =
    SET C = (i.A*i.B)

    Bil

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by billy_boi
    inserted i is called to get the values that have just been inserted by the user???
    Exactly, and those rows are the ones you want to update; not all rows in cur.
    'Inserted' behaves like a real table, you can update it in your trigger

    Did you try my suggestion?

Posting Permissions

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