10 Replies Latest reply on Aug 9, 2013 2:02 PM by Steven Hawkins

    FOR EACH ROW procedure for UPDATE

    jane_lj Novice

      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.

        • 1. Re: FOR EACH ROW procedure for UPDATE
          Steven Hawkins Master

          > 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

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

            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.

            • 3. Re: FOR EACH ROW procedure for UPDATE
              Ramesh Reddy Master

              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..

              • 4. Re: FOR EACH ROW procedure for UPDATE
                Steven Hawkins Master

                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

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

                  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.

                  • 6. Re: FOR EACH ROW procedure for UPDATE
                    Steven Hawkins Master

                    "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

                    • 7. Re: FOR EACH ROW procedure for UPDATE
                      jane_lj Novice

                      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.

                      • 8. Re: FOR EACH ROW procedure for UPDATE
                        Steven Hawkins Master

                        It needs to be a column of the update target:

                         

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

                         

                        Steve

                        • 9. Re: FOR EACH ROW procedure for UPDATE
                          jane_lj Novice

                          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.

                          • 10. Re: FOR EACH ROW procedure for UPDATE
                            Steven Hawkins Master

                            > 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