Virtual triggers

[gioppoluca]

The problem is:

"how to be aware that a content of a table is updated?"

Usually a trigger should do the trick, but when the table is modeled into a VDB how can we be "triggered" that a row has be added (or other) and react?

Can it be done, are there any advise on this?

Thanks

Luca

[rareddy]

Luca,

The next 7.4 beta3 version includes a "change data capture" feature, using which you can notify the query engine that source data has been changed, akin to mimicking the behavior of a trigger.

Can you explain how you are intending to use this?

Thanks

Ramesh..

[gioppoluca]

Said that in my company sometimes people comes to me with a problem and I try to advise on using a right tool for the right need.

The problem is merging data from different sources to create a dashboard to monitor sensor information.

Some data is stored in SQLServer that rolls a instance (I'm not a SQLServer expert ... never used ... that is the problem of having to find solution to "whatever" problems .. I may not know much of some piece of tech ;-) ) when the space/quota is filled up [Warning: I say SQL Server but it could be the sensor data gethering application that does the job, but is a black box so the result is that I end up having a rolling DB].

My first idea was to use TEIID, that is a piece of tech I'm striving to bring in for long time, for the data consolidation throught eterogeneous systems, but have the problem that when the SQLServer rolls the data I end up with no more historical data (the old instance has been renamed and I have a new empty one).

The solution I was looking for was I create a trigger in the VDB table that fires when data is inserted (being sensor data acquisition just insert) so that I can transfer all the new stuff into a persistent table into MySQL.

But ... no trigger in VDB.

This is because I do not want and cannot touch the SQL Server DB and would like not to create a business logic and schedule a process to poll the DB and do the work (also becouse I colud loose data).

Hopes this clear the use case.

If you have better advise you are welcome.

Luca

[rareddy]

Luca,

Thanks very much for the explanation.  If the inserts are always going through the VDB, then you can devise your "insert" procedure on your view to insert into SQLSever, and at the same time check to move any data to the MySQL instance. In Teiid insert is written using "virtual procedure", so you have some amount of freedom as to take all actions using the Teiid procedural language.

Hope this helps.

Ramesh..

[shawkins]

The 7.4 section on update procedures has been updated to describe them as instead of triggers and/or as before/after row triggers.  An enhancement request could be logged to cover other types of triggers, such as before/after statement and making the trigger applicable to physical tables as well.

Steve

[rareddy]

Steve,

That sounds interesting about the statement triggers, however I am failing to picture, how this can be devised? Can you give an example how somebody could define statement trigger in a update procedure?

Thanks

Ramesh..

[shawkins]

Ramesh,

Our old concept of an update procedure is roughly equalent to an instead of trigger.   I'm saying that enhancements would be needed to support before/after statement and other trigger targets.  Ideally to support triggers more broadly you'd want a proper metadata representation as schema/model entries.

Steve