-
1. Re: Creating trigger on a table
rareddy Aug 2, 2012 11:59 AM (in response to gamvi01)When you say "table", do you mean the table in source system?
-
2. Re: Creating trigger on a table
gamvi01 Aug 2, 2012 12:40 PM (in response to rareddy)Yes tables in teiid.
-
3. Re: Creating trigger on a table
rareddy Aug 5, 2012 10:37 AM (in response to gamvi01)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
shawkins Aug 6, 2012 8:25 AM (in response to rareddy)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
markaddleman Aug 6, 2012 9:20 AM (in response to shawkins)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?
-
6. Re: Creating trigger on a table
rareddy Aug 6, 2012 10:40 AM (in response to markaddleman)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
markaddleman Aug 6, 2012 12:36 PM (in response to rareddy)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
rareddy Aug 6, 2012 3:14 PM (in response to markaddleman)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
markaddleman Aug 6, 2012 3:15 PM (in response to rareddy)I didn't realize I could there there from the metadata factory. Thanks for the tips.
-
10. Re: Creating trigger on a table
shawkins Aug 6, 2012 3:40 PM (in response to markaddleman)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
rareddy Aug 6, 2012 3:59 PM (in response to shawkins)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
shawkins Aug 6, 2012 4:46 PM (in response to rareddy)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
shawkins Aug 7, 2012 1:45 PM (in response to shawkins)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
gamvi01 Aug 10, 2012 4:19 PM (in response to shawkins)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[