Perform SQL statement after each DELETE

Is there a way to make jOOQ perform a SQL statement (in my case a CHECKPOINT) after each DELETE?

Ideally there would also be a way to specify the tables that this happens for.

The RDBMS I’m using (HSQLDB) doesn’t allow this statement inside a trigger, otherwise I would have done it this way.

Answer

Is there a way to make jOOQ perform a SQL statement (in my case a CHECKPOINT) after each DELETE?

The easiest way forward would be to use an ExecuteListener which checks whether the current statement is a DELETE statement. You can do this:

• Either using a regex on the generated SQL, if you also have plain SQL statements that are not generated using the jOOQ DSL
• Or using an instanceof Delete check on ExecuteContext.query() if you’re sure that you’re only running jOOQ DSL statements.

This is to detect the execution of such a statement. Your follow up statement can be run in different ways, of course.

Ideally there would also be a way to specify the tables that this happens for.

This is a bit more tricky. You could implement your own VisitListener that finds all the delete statements on specific tables.

This only works if you use the DSL API, unless you’re willing to run a VisitListener on a jOOQ query that you parse based on your plain SQL statements, in case of which you could also do this for arbitrary other statements. Assuming that the parser can parse your SQL.