Post reply

insert trigger question

  • June 12, 2005 at 3:40 pm

    #69105

    Hey all,

    I have a table that has a tonne of rows, with several thousand being added a day. What the rows contain isn't really important for the question so i'll leave it out for now, suffice to say that they're messages with status updates etc.

    At the bottom of the insert trigger, a cache table is updated which holds the latest status of each entity.

    This is currently done via a statement not dissimilar to:

    update cachetable set status = (select status from inserted) where machineid = (select machineid from inserted)

    or something like that.

    My question is, is there a better way...

    the above statement only handles a single row in inserted and will error if there's more than one, but i think i'm missing something.

  • June 12, 2005 at 7:11 pm

    #564909

    update C set C.status = I.status from inserted I inner join CacheTable C where C.machineid = I.Machineid

    This will handle any number of rows, and will also update as little rows as necessary.

  • June 12, 2005 at 10:13 pm

    #564917

    thanks remi, that answers a question I had. wasn't aware you could use from on an update statement in t-sql... cheers.

  • June 13, 2005 at 5:09 am

    #564942

    The from clause is quite neat - can be used in deletes as well with joins, etc - we had a post about it last week from memory..  Always something to learn in these forums!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply