SQL Server Performance

Modifying a table apparently being prevented by trigger after update

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by EMoscosoCam, Nov 24, 2010.

  1. EMoscosoCam Member

    Hello
    I have a table called MyTable with an UpdateTimeStamp column (of type datetime). There is an after update trigger that supposedly updates that column whenever a certain field is changed. An application is being used to modify that field. The end-user reports that whenever the modification is attempted, the application raises an error (an ODBC error).
    if update(SomeField1) or update(SomeField2)
    begin
    update MyTable
    set
    UpdateTimeStamp = GetDate()
    from MyTable as pc
    inner join updated as u on u.PKField = pc.PKField
    where
    IsNull(pc.SomeField1,'') <> IsNull(u.SomeField1,'') or
    IsNull(pc.SomeField2,'') <> IsNull(u.SomeField2,'')

    end
    Let's suppose that the application locks the table when it tries to modify it in such a way that the trigger cannot put a value in the UpdateTimeStamp column. Having a parallel table MyChangeTable (PKField, UpdateTimeStamp) would solve my problem?
    Or there is something that I can do to the trigger in order to prevent the error? Any ideas?
    Thanks a lot.
  2. Adriaan New Member

    Not sure why you're referring to updated - the snapshot is called inserted. So have you looked at the actual error message?
  3. EMoscosoCam Member

    If I recall correctly, the error was something like "Invalid Cursor Operation".
    The trigger is defined as AFTER UPDATE. The idea of this trigger is to place the change time into UpdateTimeStamp whenever SomeField1 or SomeField2 are actually modified. So, if the users put the same values for either field, the trigger does not time stamps this action -and also changes to other fields are ignored. So I compare the previous values against the updated ones.
  4. Adriaan New Member

    You'll have to give the full error message.
    Also, does the trigger code involve any cursors?
  5. satya Moderator

  6. Adriaan New Member

    From the dark recesses of my memory, sometimes when an indexed view is created with some settings not specified (like ARITHABORT - which one often skips) then inserts/updates/deletes against an underlying table may cause some weird errors.
  7. EMoscosoCam Member

    Thanks for the reply.
    The trigger code is a simple update statement without a cursor. That is, whenever certain columns are updated, the trigger attemps to update the column UpdateTimeStamp.
    However, presume for a moment that the user application updates the table by using a cursor, would it prevent the trigger to make updates on the same rows fetched by the cursor?

Share This Page