Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Question Unanswered: How is represented the Updated Table in a Trigger?

    Hello

    I am a newbie creating triggers and I would like to know what is the name of the updated logical table. That is, since the are identifiers like "inserted" and "deleted" that represent the inserted and deleted rows, respectivaly, I presumed that "updated" existed - but it does not.

    I have a table with a column named "UpdatedTimeStamp" which I would like to update anytime the other columns are modified. I intended to create the following trigger:

    create trigger myTrigger
    on myTable
    after update
    as

    update updated set UpdateTimeStamp=GetDate()

    Of course, "updated" is not a valid identifier that represents the updated row.

    Please, help me in creating this trigger. What is the correct way of doing a trigger like this?

    Thanks a lot in advanced.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have books online?

    Its inerted and deleted
    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
    Mar 2003
    Posts
    144

    Question

    I know, but as I stated in my previous post, I do not know how to update a row which has just been updated. For example: I have a table with 2 columns, A and B -where B is a datetime column. I would like that when A has just been updated (not inserted or deleted), B would have the datetime value when that happened. Something like:

    update [updated] set B=GetDate()

    Of course, [updated] is not valid identifier for a logical table. Can you please help me in how to accomplish this scenary?

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The way you tell a row or rows have been updated is that both the inserted and deleted tables contain data. On an insert, the data only appears in the inserted table. On a delete, the data only appears in the deleted table.

    Now with this information, try and put together a trigger for an update, and come back with your results. Also, you might want to include some schema information, so we can see the table or tables you are trying to maintain.

    And remember, you want the trigger to update all rows updated, not just one of them. Think about set based operations.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Brett Kaiser
    Do you have books online?

    Its inerted and deleted

    How long have you been working in the bomb disposal unit at your office?



    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Mar 2003
    Posts
    144

    Question Trigger strange behaviour

    I came up with the following solution:

    suppose the existence of a table called myTable, having the columns:
    - ID numeric (PK)
    - Value varchar(50)
    - UpdateTimeStamp datetime

    Now, my trigger is

    create trigger myTrigger
    on myTable
    after update
    as

    if update(Value)
    begin
    update myTable
    set
    UpdateTimeStamp=GetDate()
    from myTable
    inner join inserted i on i.ID=myTable.ID
    inner join deleted d on d.ID=myTable.ID
    end

    Is this code ok?

    If I open the table and modify a row, the UpdateTimeStamp column is updated as expected. However, when I update only some rows with a query, for a reason I failed to understand, all the rows get their UpdateTimeStamp field updated.

    What could be wrong?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Personally, I would not bother with the UPDATE() function. It can be misleading, especially when dealing with interfaces that my issue spurious update statements, as it merely indicates whether a column was part of an update statement, and does not indicate whether the value actually changed.

    For instance, this code:
    Code:
    update A set Value = 5 where Value = 5
    ...would result in the UPDATE() function indicating that a set of records were updated, whereas in reality they have the same value as before.


    If you really want to check whether the value has changed, try something like this:
    Code:
    begin
    update	myTable
    set	UpdateTimeStamp=GetDate()
    from	myTable
    	inner join inserted i on i.ID=myTable.ID
    	inner join deleted d on d.ID=myTable.ID 
    where	inserted.Value <> deleted.Value
    	or inserted.Value is null and deleted.Value is not null
    	or inserted.Value is not null and deleted.Value is null
    ...or use the BINARY_CHECKSUM() function to quickly compare multiple columns.
    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
  •