Post reply

Resolving a deadlock

  • March 6, 2013 at 3:22 pm

    #1594194

    That would be ideal. The problem is there are many places that update or insert into the patient table. And no one can tell me all the places. They may not all be in stored procs. So If I don't have the trigger it could be missed somewhere and cause other issues.

  • March 6, 2013 at 3:28 pm

    #1594195

    Just had this same question on another thread with a trigger and doing a large insert...almost verbatim from the other thread:

    One thing I can say is that if you were to alter your trigger to skip the update and you supplied the correct DOB in your INSERT statement you would save yourself a ton of I/O. You would need to do some impact analysis however to ensure a change like this would not compromise your data should other inserts or updates supply invalid values for that column thereby circumventing the usefulness of the trigger. If that were a concern there are other things you could do with CONTEXT_INFO to skip the work in the trigger for only your batch process.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 6, 2013 at 3:34 pm

    #1594197

    How do you determine the Context_info? I don't understand how I could use that to avoid the trigger?

  • March 6, 2013 at 3:55 pm

    #1594200

    You would modify your proc to set CONTEXT_INFO before you did the UPDATE.

    SET CONTEXT_INFO 0x1256698456

    Then in your trigger you would check CONTEXT_INFO and if you found it had the same value you set in your proc you would skip the UPDATE:

    SET @var = CONTEXT_INFO();

    IF @var != 0x1256698456

    BEGIN

    -- do update as usual

    END

    http://msdn.microsoft.com/en-us/library/ms180125(v=sql.90).aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 6, 2013 at 4:09 pm

    #1594205

    Thats worth a shot. I'll do some testing. Thanks

  • March 6, 2013 at 4:56 pm

    #1594217

    Why not combine not updating the time in DOB with this?

    IF UPDATE(DOB)

    BEGIN

    UPDATE P

    SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB)

    FROM dbo.Patient P INNER JOIN

    inserted i ON

    P.PatientID = i.PatientID

    where i.DOB <> cast(convert(varchar,i.DOB,112) as datetime)

    SET @error_var = @@ERROR

    --Error checking

    IF @error_var > 0

    GOTO rollback_tran

    END

  • March 6, 2013 at 5:09 pm

    #1594220

    Thanks!! That may be the best way to fix it. Or a variation at least.

Viewing 7 posts - 16 through 21 (of 21 total)

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