FOR EACH ROW procedure for delete

[jane_lj]

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.

[rareddy]

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.

[jane_lj]

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.

[shawkins]

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?

[jane_lj]

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.