FOR EACH ROW procedure for UPDATE

[jane_lj]

Hi,

Our view model created in Teiid Designer have to support updates, and we cant use the default UPDATE and INSERT. I have figured out how to write FOR EACH ROW procedure for INSERT.

But for UPDATE, if the user gives the UPDATE SQL againt the view has WHERE conditions, we don't know how to get WHERE condition info in procedure generically, becaue we don't know what kinds of condiions user will give when i write procedure.

Thanks.

[shawkins]

> we don't know how to get WHERE condition info in procedure generically, becaue we don't know what kinds of condiions user will give when i write procedure

Keep in mind that the logical processing model here effectively issues a select against the view using the WHERE clause specified in the UPDATE/DELETE and then for each affected row executes the trigger action.  So you do not need to know exactly what forms of predicates that the user will specify, just how to handle the modification of each row.  To do this you use the new (used for the new value in insert/update), old (used for the old value in update/delete) and changing variables (used to denote that an insert value or update change clause was used against that column).  The trigger logic for an update for example likely check the changing.column values to see what columns are being modified and make appropriate decisions from there.

Steve

[jane_lj]

Steven,

Sounds like when the call flow runs to the point of the trigger procedure, the WHERE clause part has been filtered. But from the following example I tried, the trigger action applied to all of the rows:

I have table PetrelBorehole in soure model Petrel_SM, have table WellEntire in view model, my procedure is this:

FOR EACH ROW

BEGIN ATOMIC

          IF(CHANGING.well_operator)

          BEGIN

    UPDATE Petrel_SM.PetrelBorehole SET Operator = "NEW".well_operator;

          END

END

The UPDATE statements is this:

UPDATE WellEntire set well_operator = 'AAA' where uwi = 'B1'

The result should be only update one row whose uwi = 'B1'. But actually all rows got updated.

Can you see anywhere I did incorrectly?

Thanks a lot.

[rareddy]

Jane,

So, since the trigger is executed once per each row, you need make you use of your primary keys to filter out to the row which you want to update.

For ex. if uwi is PK on the table

FOR EACH ROW
BEGIN ATOMIC
          IF(CHANGING.well_operator)
          BEGIN
    UPDATE Petrel_SM.PetrelBorehole SET Operator = "NEW".well_operator WHERE uwi = "OLD".uwi;
          END
END

Since PK does not updated, and you are using old value, it will update the correct row.

Ramesh..

[shawkins]

And just to take what Ramesh is saying one step further, you may need to account for all possible changes against a target, for example with two or more possible columns change you would have:

IF (CHANGING.col1 OR CHANGING.col2 ...)

BEGIN

     UPDATE ... SET col1 = "NEW".col1, col2 = "NEW".col2 ... WHERE pkcol1 = "OLD".pkcol1 ...;

     ...

END

[jane_lj]

Ramesh and Steven,

Recently we wrote the UPDATE prodecure as the syntax you told, the record got updated in VDB, but the filter doesn't work, all records got updated, should be only one. Any new changes since you told us this syntax?

This is the procedure we wrote:

IF (CHANGING.col1 OR CHANGING.col2 ...)

BEGIN

     UPDATE ... SET col1 = "NEW".col1, col2 = "NEW".col2 ... WHERE "NEW".pkcol1 = "OLD".pkcol1 ...;

     ...

END

(I added "NEW" in WHERE clause, because designer complains if I don't)

This is the update SQL we ran:

UPDATE tablename set col1= 'AAA', col2='BBB' where pkcol1= 'B1'

Please advise.

Thanks.

[shawkins]

"NEW".pkcol1 = "OLD".pkcol1 simplly checks if the pkcol1 has changed for the row.

WHERE "NEW".pkcol1 = "OLD".pkcol1 ...

assuming table tbl, it should be

WHERE tbl.pkcol1 = "OLD".pkcol1 ...

Steve

[jane_lj]

Thanks, Steven.

I tried both tbl.pkcol1 and VM.tbl.pkcol1, designer gave me this error:

ERROR: TEIID31119 Symbol tbl.pkcol1 is specified with an unknown group context

ERROR: TEIID31119 Symbol VM.tbl.pkcol1 is specified with an unknown group context

Please advise.

Thanks.

[shawkins]

It needs to be a column of the update target:

UPDATE x .... where x.pkcol1 = "OLD".pkcol1 ...

Steve

[jane_lj]

Thank you so much, Steven. It works now.

BTW, if in WHERE clause of update SQL, the columns are not PK columns, even several conditions combine, can this procedure also work? Or need to revise the procedure?

Thanks.

[shawkins]

> if in WHERE clause of update SQL, the columns are not PK columns, even several conditions combine, can this procedure also work?

Regardless of the where clause (if any) used in your user sql, it will first determine the affected row set and then for each row process it against the instead of trigger.  You may put any statements/where clauses you like in your instead of trigger it will simply execute for affected row.

Steve