Introducing HCL Informix Smart Triggers

                                                                                                            Updated: 8/30/18
​
Databases are historically a great place to store data and retrieve data. However, they are not always the easiest to process events and receive alerts from, especially in real time or near real time. You can use a specialized software for this, but what if you wanted the best of both worlds? An enterprise level database for storage and security, but with the ability to process events and alerts as they happen.

​What are Smart Triggers?
It’s a new feature introduced as part of the JDBC driver in 4.10.JC9 and in the corresponding server 12.10.xC9. Smart Triggers are special ‘push’ notifications from the HCL Informix server to your Java application.  This push notification is backed by a selective trigger that you create from your client application (it’s not a normal database table trigger) that will push an event to the client when your trigger criteria happens.  No more polling for events when you want to know if something has changed.

Let’s take an example. Say you have an accounts table that stores how much money a user has.  You would like to have your application be alerted, triggered when a balance goes below amount.  For our application to know this, we would have to poll for that, or write a custom routine in the server that can somehow signal our application that this happens.

With this new feature the application itself can now register a Smart Trigger to be alerted when the balance goes below the target amount.  What makes Smart Triggers nice is that they require no schema changes, no custom code for the server (user defined routines), and can be created and destroyed purely from an application. Since Smart Triggers use the push notification back-end feature of the server, all the processing for our triggers is done on the server and the application itself remains thin and lightweight.  You can register many triggers and keep your application footprint small.

The triggers themselves are based on simple select SQL statements.  A simple select statement in this case is a query on exactly 1 table, with basic projection and predicate clauses.
Comparing Smart Triggers with a regular table trigger

​Smart Triggers in Action
For this let’s take our banking example with user accounts.  We have a very simple schema.

​Now we can write a few lines of Java code to create our Smart Trigger.

Notice that other than passing in the URL for our database, we can go straight to adding a Smart Trigger.  There are several optional settings for Smart Triggers, but you can get started without them.  Details on all the options are covered in the Javadoc that ships with the JDBC driver. With the addTrigger(…) method we can choose the table/owner/database and specify the query we want to run.  This query is our filter.  We will only get notified on changed rows (insert/update/delete) that match this condition.  The final part of a Smart Trigger definition is a callback you provide.  With that callback (a simple implementation of the IfmxSmartTrigger interface) you will get a string object back from the Smart trigger with information about the row(s) and transactions involved that met the criteria of the trigger.

​When a trigger is executed, the notify method will get called, and you can process the data to take whatever action you want.  The String JSON you get back looks like this:

Different operations (update vs insert) and whether it was part of a transaction will alter the JSON structure slightly when returned.

What's Next?
In future versions, we look to incorporate feedback we got from our first implementation.  We look to include detachable triggers so a client can disconnect and reconnect without losing any records.  We are also looking to improve the auditing capability by adding the user ID who initiated the change to the data.  And as always, we welcome additional features/comments/suggestions.
​
Conclusion
We hope that Smart Triggers can provide new and interesting ways to interact with your data, from monitoring to selective data streaming, you now have a real-time accessor to your data.

Brian Hughes
​Software Engineer
HCL Informix
Connect with me on LinkedIn

Informix is a trademark of IBM Corporation in at least one jurisdiction and is used under license.
​