When you say "table", do you mean the table in source system?
Yes tables in teiid.
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
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.
We have a bunch of tables that are brought in through the DB2 execution factory. A couple of questions:
- Any reason not to mark them all as virtual?
- As a subclass of the DB2 execution factory, can you think of a convenient way to set all the tables as virtual?
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.
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?
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.
I didn't realize I could there there from the metadata factory. Thanks for the tips.
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.
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.
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?
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,
CREATE TRIGGER ON user INSTEAD OF INSERT
AS FOR EACH ROW
insert into DEMO VALUES('1','TEST',10);
2) Can we create trigger this way in teiid 7.7
<model name="TrigView" type="VIRTUAL">