Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Question Unanswered: Trigger by Column Update?

    I have a table that records three different codes, each in it's own field. These codes have a corresponding Date field indicating when the code was changed.

    Code1, Date1
    Code2, Date2
    Code3, Date3

    Seems like having a trigger update these dates would be a good idea, but if I do an update trigger, how do I do it so that if Code1 is changed, then only Date1 is updated? Can I reference the old and new values of the record in the trigger?

    Thanks a lot.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes,

    Look at trigger in bol

    but basically, where an action occurs against the table, sql server creates 2 virtual tables

    inserted and deleted

    inserted holds new records or the data from an update

    deleted holds deleted rows or the before image of the update

    CREATE TRIGGER myTrigger ON myTable
    FOR UPDATE
    AS
    UPDATE m SET myDate = GetDate()
    FROM inserted i INNER JOIN myTable m
    ON i.Code = m.Code

    something like that....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    3

    dumb question coming...

    sorry, but what's bol?

    Also, I'm not sure about the ON part... But bol should clear that up (right?)

    But knowing that I can use INSERTED to refer to the new data is huge.

    Thanks a lot.

  4. #4
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: dumb question coming...

    Book Online. Most of information you need regarding SQL.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: dumb question coming...

    Originally posted by heboardman
    sorry, but what's bol?

    Also, I'm not sure about the ON part... But bol should clear that up (right?)

    But knowing that I can use INSERTED to refer to the new data is huge.

    Thanks a lot.
    ON relationally joins the data in the base table to the inserted table based on some key

    you need to do this since inserted may have 1 or many rows...

    Sorry bol is short for books online

    If you don't have a copy, you can look at

    http://msdn.microsoft.com/library/de...start_4fht.asp

    But you should have sql server client side tools installed on your machine...and it comes with bol...

    I don't think I ever close it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2003
    Posts
    3
    Ok, I'm learning a whole lot here.

    But basically my trigger will have to have three IF statements, comparing each inserted code with the previous code and if they're not the same, update the proper date field.

    Something like...

    CREATE TRIGGER myTrigger ON myTable m
    FOR UPDATE
    AS

    IF UPDATE(Code_1)
    BEGIN
    UPDATE m SET myDate! = GetDate()
    FROM inserted i INNER JOIN myTable m
    ON i.ID_Code = m.ID_Code
    END

    IF UPDATE(Code_2)
    BEGIN
    UPDATE m SET myDate2 = GetDate()
    FROM inserted i INNER JOIN myTable m
    ON i.ID_Code = m.ID_Code
    END

    IF UPDATE(Code_3)
    BEGIN
    UPDATE m SET myDate3 = GetDate()
    FROM inserted i INNER JOIN myTable m
    ON i.ID_Code = m.ID_Code
    END

    And nothing special to end it? Does this sound like the way to approach it?

Posting Permissions

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