6 Replies Latest reply on Sep 19, 2012 2:46 PM by Mark Addleman

    Defining a trigger definition

    Mary Ann Furno Newbie

      I'd like to define a triggerDefinition that looks something like the following:

       

       

         <triggerDefinition>
          DECLARE string setclause = ' ';
                     DECLARE string finalsetclause = ' ';
          FOR EACH ROW
          BEGIN
           IF (CHANGING.NAME)
            setclause = setclause + ',' + security_ldap.baseuser.NAME = new.name;        
           IF (CHANGING.PHONE)    
            setclause = setclause + ',' + security_ldap.baseuser.PHONE = new.phone;
          END

          BEGIN UPDATE security_ldap.baseuser SET finalsetclause WHERE security_ldap.baseuser.DN=new.userid and security_ldap.baseuser.SYSID=new.sysid;END    
                END
       
                    </triggerDefinition>

       

      I am currently getting the following error:

      [21:58:37.440][info][talledLocalContainer] Caused by: [QueryPlannerException] ERR.015.008.0045: Error Code:ERR.015.008.0045 Message:Error Code:ERR.015.008.0045 Message:Failed parsing Update plan for SECURITY.BASEUSER

      [21:58:37.440][info][talledLocalContainer] 1 [QueryResolverException] ERR.015.008.0045: Error Code:ERR.015.008.0045 Message:Failed parsing Update plan for SECURITY.BASEUSER

      [21:58:37.440][info][talledLocalContainer] 2 [QueryParserException]Parsing error: Encountered "DECLARE" at line 1, column 1.

      [21:58:37.440][info][talledLocalContainer] Was expecting one of:

      [21:58:37.440][info][talledLocalContainer]     "create" ...

      [21:58:37.440][info][talledLocalContainer]     "for" ...

      [21:58:37.440][info][talledLocalContainer]    

      [21:58:37.440][info][talledLocalContainer]            at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:182)

       

      How do I get past this problem?  I can see that I can create a procedure but the documentation cites virtual procedures and then states that they are only supported in Teiid Designer.

        • 1. Re: Defining a trigger definition
          Ramesh Reddy Master

          Is this in Dynamic VDB? If yes, look for DDL Metadata section in the Reference Guide as to how to define the Virtual Procedure. As per trigger above you need to start out with "

          CREATE TRIGGER ON CustomerOrders INSTEAD OF [INSERT|UPDATE|DELET] AS..."

           

          Ramesh..

          • 2. Re: Defining a trigger definition
            Mark Addleman Master

            Hi, Ramesh -

             

            MaryAnn is part of our project.  Unfortunately, we have not yet upgraded our stuff to support the DDL Metadata feature.  Our own XML format has a triggerDefinition tag that routed into a Table.setTriggerDefinition call.

             

            My guess is if the trigger is a stored procedure, we'll need to build a stored procedure object using the MetadataFactory and then call Table.setTriggerDefinition("CALL myStoredProcName") ?

            • 3. Re: Defining a trigger definition
              Ramesh Reddy Master

              Thanks Mark for the info.

               

              The CREATE TRIGGER was supported before the DDL metadata. So see this, http://docs.jboss.org/teiid/7.7.0.Final/reference/en-US/html_single/#d0e1660

               

              The DDL feature here only helps in terms of configuration based metadata, so using the XML as you are doing just call the same trigger definition using

               

              Table.setInsertPlan("CREATE TRIGGER..")

              Table.setUpdatePlan("CREATE TRIGGER..")

              Table.setDeletePlan("CREATE TRIGGER..")

               

              If you have already defined and deployed the VDB, then you can even directly issue the "CREATE TRIGGER.." DDL command directly using the JDBC statement. However, note that the changes are not persisted and will not survive the server restart. There is extension mechanism, where you can provide the that layer, however Teiid did not provide any default mechanism for persistence. Look for MetadataRepository extension in the Teiid documents.

               

              HTH.

               

              Ramesh..

              • 4. Re: Defining a trigger definition
                Mark Addleman Master

                Got it.  We'll give that a try. 

                 

                On a separate, but releated note, one of the things that keeps us from switching over completely to the DDL metadata is our use of Apache Velocity.  Many of our view definitions are actually Velocity scripts which generate the real DDL.  As indicated in another post, the trigger, in this case, must handle a few hundred columns so generating the trigger definition using a macro language is pretty much the only way to go.

                 

                To facilitate moving over to Teiid's DDL metadata feature, what do you think about adding an extension point for a string processing callback mechanism?  I imagine we would register a callback (per VDB?) that Teiid would invoke for every DDL string in the metadata.  In our case, we'd run it through our Velocity engine and pass back the instantiated template.

                • 5. Re: Defining a trigger definition
                  Ramesh Reddy Master

                  Mark,

                   

                  In 8.x, see the MetadataRepository framework. To give an example DDL is one implementation of this interface, another we use Designer based indexes, another is native metadata load from sources. More over they can be chained. So, in your case you can provide a MetadataRepository that can process the velocity scripts and provide the metadata directly without even touching the DDL.

                   

                  Ramesh..

                  • 6. Re: Defining a trigger definition
                    Mark Addleman Master

                    I haven't had a chance to study the MetadataRepository framework yet but chaining repositories sounds like the ideal solution.   Thanks!