4 Replies Latest reply on Mar 19, 2013 2:41 PM by jane_lj

    FOR EACH ROW procedure for delete

    jane_lj Novice

      Hi,

       

      I'm write FOR EACH ROW procedure for delete, I saw this info from docs:

       

      " For UPDATE and DELETE statements this will be every row that passes the WHERE condition..."

       

      My understanding is only the rows pass the WHERE condition, will go through FOR EACH ROW procedure, so my procedure is:

       

      FOR EACH ROW

      BEGIN ATOMIC

                DELETE FROM Petrel_SM.PetrelBorehole;

      END

       

       

      Then I give the WHERE condition in DELETE sql againt view model:

       

      delete from wellentire where common_well_name = 'A10'

       

      So only one row shoule be deleted, but now all rows are deleted.

       

      Looks like my understanding is wrong.

       

      I have to add WHERE condtion in my FOR EACH ROW procedure? Can anyone give an example for how to take WHERE conditon(s) for all the cases generically?

       

      I tried this:

       

      FOR EACH ROW

      BEGIN ATOMIC

                DELETE FROM Petrel_SM.PetrelBorehole WHERE Petrel_SM.PetrelBorehole.UWI = "OLD".uwi;

      END

       

      But this way can't take all cases, because the procedure won't know which will be in WHERE conditions.

       

      Thanks.

        • 1. Re: FOR EACH ROW procedure for delete
          Ramesh Reddy Master

          I believe the default "delete" procedure works the way you are saying. However if override that, then it up to your transformation how to manage the range of deletes.

          • 2. Re: FOR EACH ROW procedure for delete
            jane_lj Novice

            Thanks, Ramesh. It's good to know my understanding is correct.

             

            We didnt' override it, and no need to do it. Can you think out any reason that doesn't take the WHERE condition?

             

            We are using Teiid Designer 8.0 and Teiid 8.3. Any conflict between this two versions?

             

            Thanks again.

            • 3. Re: FOR EACH ROW procedure for delete
              Steven Hawkins Master

              Just to recap, when you use a for each row trigger, the trigger body will be executed for each row and you must define the trigger in such a way that only affects the row(s) that you want.  So in your example above having a for each row trigger with a delete without a where clause will delete all rows for every row affected by the view delete - which is obviously not what you want.

               

              As Ramesh says if the view supports the notion of being inherently updatable, then Teiid should figure out the appropriate delete action for you without you needing to define a for each row trigger.

               

              > Can you think out any reason that doesn't take the WHERE condition?

               

              Can you describe this more fully?

              1 of 1 people found this helpful
              • 4. Re: FOR EACH ROW procedure for delete
                jane_lj Novice

                Steven,

                 

                Thank you very much for your reply. Your explanation is helpful for me.

                 

                You made me clear now, so for my case, I can just use the default DELETE tab, even no need to do anything for that tab.

                Now I can understand Ramesh's comments too.

                 

                Before read your explanation, I thought I have to customized each tab now since I customized INSERT tab. Actually I don't need.

                 

                So now, for UPDATE, I do need to write FOR EACH ROW to override it, because I need to do some convert for some columns between View model and Source model.

                 

                If I only want to update some rows which satisfy WHERE condition, how should I give those condition in procedure to take care of all cases, my headache is I don't know what kinds of condiion user will give when i write procedure.

                 

                Thanks again.