1

I'm trying to implement some application level data triggers that will replace existing DB triggers. These are very simple triggers defined by "when field F1 is updated in table T1 do X". I use JOOQ for persistence layer, triggers are implemented as ExecuteListener.

I'm overriding executeEnd(), and getting the Query object from the context. I can see that the implementation - UpdateQueryImpl (for update) has a very convenient updateMap, that I could potentially examine in order to find which fields changed and what is the new value. However I couldn't find any public method to access it.

Of course I could try to parse the SQL string but this seems much more error prone. And I'm trying to avoid using reflection unless I really have to, for all the obvious reasons.

Any other ideas?

I use Jooq version 3.10.8, DB is postgres 9.6

Amit Goldstein
  • 827
  • 9
  • 18
  • 1
    Why do you want to replace those triggers? – Lukas Eder Aug 13 '19 at 08:02
  • @LukasEder There are several business requirements that I'm afraid will be hard to maintain using triggers - audit logging in a specific format (sure I can log from trigger but will not have the full context), cycle detection, authorization (which requires some domain knowledge), feature toggling. Also the creation of triggers themself is not easy for Java devs, mostly not very experienced with PLSQL. If JOOQ could only support trigger creation.. ;-) – Amit Goldstein Aug 14 '19 at 07:25
  • With all the above being said, I am looking into creating a frameworks that will allow devs to create such triggers, with all the cross cutting concerns baked into the triggers, using Java flyway classes and some Jooq (for creating the expressions). It's a dreaded task but probably less than wrapping my head over VisitListener. – Amit Goldstein Aug 14 '19 at 07:31
  • There are some means to provide PostgreSQL with some additional context: https://stackoverflow.com/q/1934050/521799. jOOQ will soon support the creation of triggers: https://github.com/jOOQ/jOOQ/issues/6956, but first we had to "just" support the entirety of procedural languages, which we're still working on :) And you'd still need to use the procedural syntax through jOOQ, so you wouldn't gain too much. Triggers are a much better choice, because otherwise, you'll rely on every action going through jOOQ. Imagine a quick migration script written in Perl... – Lukas Eder Aug 15 '19 at 07:28

1 Answers1

2

Generally speaking DB server features should typically be preferred over client-side features. In the case of triggers, there is for instance always the chance that you miss an UPDATE to a given table. E.g. if you have a jOOQ query using plain SQL, another application using plain JDBC or JPA, or some SQL script (e.g. migration), or an update triggered by a MERGE, etc.

If you don't want to use triggers you could also use PostgreSQL's NOTIFY mechanism. See here and here for some notes on this.

This being said, there are also some options offered by jOOQ. The ExecuteListener SPI has, as you have discovered, no API to traverse the SQL query. There are plans to address this in a future release, but for now you would have to couple your ExecuteListener with a VisitListener. This is a very powerful SPI, but not for the faint-hearted, as it is at the same time quite low-level. See also this blog entry for an example.

For completeness sake I should also mention jOOQ's RecordListener. This would be applicable if you only need notifications from changes made using UpdatableRecord operations.

knutwannheden
  • 680
  • 4
  • 7
  • I will definitely look at VisitListener. The problem with Notify, if I understand it correctly, is that it will not run in same transaction as the triggering code, which is essential to my solution. – Amit Goldstein Aug 01 '19 at 06:54