1 2 3 Previous Next 32 Replies Latest reply on Aug 31, 2012 4:53 PM by Vineela Gampa

    Creating trigger on a table

    Vineela Gampa Novice
        • 1. Re: Creating trigger on a table
          Ramesh Reddy Master

          When you say "table", do you mean the table in  source system?

          • 3. Re: Creating trigger on a table
            Ramesh Reddy Master

            Tables in Teiid are either referred either as virtual tables or virtual views. Yes, you can define trigger on them. Please note that these changes are not persistent, so they will not last restart.  If you need that functionality then you need to look into extending MetadataRepository http://docs.jboss.org/teiid/7.7.0.Final/developer-guide/en-US/html_single/#d0e3110

             

            Ramesh..

            • 4. Re: Creating trigger on a table
              Steven Hawkins Master

              Just to clarify, no you cannot create a trigger on a Teiid physical table.  We expect a Teiid view/virtual table to be used, which could be just defined as "SELECT * FROM TBL". 

               

              This is similar to the limitation of expecting a virtual table for materialization.  I think there is an existing enhancement request to allow these types of operations on physical tables.

              • 5. Re: Creating trigger on a table
                Mark Addleman Master

                We have a bunch of tables that are brought in through the DB2 execution factory.  A couple of questions:

                1. Any reason not to mark them all as virtual?
                2. As a subclass of the DB2 execution factory, can you think of a convenient way to set all the tables as virtual?
                • 6. Re: Creating trigger on a table
                  Ramesh Reddy Master

                  You can leave the Tables from DB2 execution factory as is, and using delegating execution factory or extending the db2 execution factory, you can create what we call "base transformation layer" where you define a view for every physical table with transformation like "select * from physical". Then once you have that, then you can define the triggers on that view table.

                   

                  Ramesh..

                  • 7. Re: Creating trigger on a table
                    Mark Addleman Master

                    As a subclass, how can I get a list of all of the tables read in by the JDBCMetadataProcessor?  One option might be to make a copy of JDBCMetadataProcessor and alter it to expose the data that we need, then override getMetadata() to use the altered JDBCMetadtaProcesorr.  Is there some less intrusive solution?

                    • 8. Re: Creating trigger on a table
                      Ramesh Reddy Master

                      Override the "getMetaData()" call, and call super.getMetadata(), once that completes, on the "MetadataFactory" instance, use "getSchema" call, then you can read all the tables on it. Now add more View tables on the same schema object/ or Metadatafactory in the overridden method according all the tables read. There is no restriction that source model means it can only contain the physical tables, it can also have view tables on it. Designer introduced that concept to keep the source tables separate from view tables, but it is not necessary.

                       

                      Ramesh..

                      • 9. Re: Creating trigger on a table
                        Mark Addleman Master

                        I didn't realize I could there there from the metadata factory.   Thanks for the tips.

                        • 10. Re: Creating trigger on a table
                          Steven Hawkins Master

                          Unfortunately that's not quite what we're currently expecting.  The MetadataValidator explicitly checks that physical only contain physical and virutal only contain virtual.  There are also a couple of places in the code that may need to be vetted to relax that condition.

                          • 11. Re: Creating trigger on a table
                            Ramesh Reddy Master

                            I think we should get rid of that validation. With current model scope, not allowing physical tables on virtual model makes sense, not the other way around. I am more more liking the idea of construct "create [foreign] schema blah" to replace the model terminology, so that we can just define schemas and define metadata under that schema.

                             

                            Ramesh..

                            • 12. Re: Creating trigger on a table
                              Steven Hawkins Master

                              Yes, that's exactly what we're talking about with Barry.  However we won't get to flexible/declarative schemas in 8.x since there's too much in our metadata objects, usage of vdb.xml, and surrounding logic that would have to change. 

                               

                              I had worried about this usecase since we had directed Mark et. al. to use virtual physical records - since we did not validate that in pre 8.0 releases.  However on the flip side we didn't fully vett allowing it either, so I was fine when the validation was added in 8.0.  Mark can you create an issue to allow virtual stuff on physical schema if you need it?

                               

                              Steve

                              • 13. Re: Creating trigger on a table
                                Steven Hawkins Master

                                I went ahead and worked https://issues.jboss.org/browse/TEIID-2136 since this is valuable when using getMetadata from physical sources.

                                 

                                Steve

                                • 14. Re: Creating trigger on a table
                                  Vineela Gampa Novice

                                  How to mark a table as updatable through vdb ?

                                   

                                  what are the different ways to create trigger on teiid 7.7. I tried below but i keep getting below error.

                                   

                                  [13:12:21.392][info][talledLocalContainer] 10 Aug 2012 13:12:21,392 PDT WARN  [PROCESSOR] Processing exception 'Parsing error: Encountered "<EOF>" at line 4, column 90.

                                  [13:12:21.392][info][talledLocalContainer] Was expecting one of:

                                  [13:12:21.392][info][talledLocalContainer]     "option" ...

                                  [13:12:21.392][info][talledLocalContainer]     ";" ...

                                  [13:12:21.392][info][talledLocalContainer]     ' for request dJ06XZ8agkb7.0.  Exception type org.teiid.api.exception.query.QueryParserException thrown from org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:194). Enable more detailed logging to see the entire stacktrace.

                                   

                                   

                                  I have a view named user with the below definition.

                                   

                                   

                                  CREATE VIEW user (zip, phone, name, userid, sysid)

                                  WITH CASCADED CHECK OPTION;

                                   

                                   

                                  CREATE TABLE DEMO (

                                  ID Long DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_D3EE668F_2FFD_4552_ADA7_97DA41273C70) NOT NULL,

                                  MSG String(100),

                                  Host_ID String(10)

                                            );

                                   

                                   

                                  CREATE TRIGGER ON user INSTEAD OF INSERT

                                                                AS FOR EACH ROW

                                                                BEGIN ATOMIC

                                                                          insert into DEMO VALUES('1','TEST',10);

                                                                END;

                                   

                                   

                                  2) Can we create trigger this way in teiid 7.7

                                   

                                  <model name="TrigView" type="VIRTUAL">

                                            <metadata type="DDL"><![CDATA[

                                  1 2 3 Previous Next