Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL and transaction isolation

Back to Forum: Tech Off
  • User profile image
    PerfectPhase

    Wondering if anyone can shead some light on this, I have three tables, Users, Tokens and User_Token.  The latter having foreign key contrants on the first two and they have cascaded deletes enabled.

    On User_Token I have a SQL-CLR trigger (FOR DELETE) that fires when a row is deleted, all works well. 

    The problem is that this trigger needs to reference some of the information via the foreign keys.  If the delete is done on one of the primary tables, even though the FOR DELETE trigger on the primary fires after the FOR DELETE on User_Token, the referenced record is not visable to the User_Token trigger. I guess the flow is

    BEGIN TRANS
    DELETE FROM USER
       DELETE FROM USER_TOKEN
          FIRE USER_TOKEN TRIGGER
    FIRE USER TRIGGER
    COMMIT TRANS

    Is there a way to change the transaction isolation (and if so to what) so that the cascaded deletes can still see the uncommited (soon to be deleted) record outside the transaction?

    Failing that I will have to remove the CASCADED DELETE and added an INSTEAD OF DELETE trigger to the primary to do the deletion.  I would prefer to stick with the casscade though.

    BTW all the queries are being done over 'connection context=true'.

    Thanks

    Stephen.

  • User profile image
    Maddus Mattus

    If you want to see "dirty" records in your other query, you should edit isolation level there. Not in this query.

  • User profile image
    PerfectPhase

    I want to see dirty reads from within the context of the the trigger activated by the cascaded delete if that's possible?

  • User profile image
    Maddus Mattus

    Why not change the trigger to a for delete instead of an after delete? That should solve your problem.

  • User profile image
    PerfectPhase

    AFTER and FOR are the same thing.

    http://msdn2.microsoft.com/en-us/library/ms178134.aspx

    I have done what I need to do with an INSTEAD OF trigger, but was still intreasted how to read the uncommited data.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.