Default field value lost in RecordListener callback insertEnd()

16 views
Skip to first unread message

Nicholas Hu

unread,
Oct 9, 2015, 7:11:14 AM10/9/15
to jOOQ User Group
Hi guys,

I am implementing my own record listener to catch all insert/update/delete operations and insert a corresponding record to a audit table with the same values in addition to serveral self-defined fields like Hibernate. 

However, I found the default value defined in MySQL is lost or not updated when insertEnd() is called in my own record listener. e.g. I defined a table with a field 'STATUS' with definition:

CHAR(1) not null default "A" 

When a record is inserted into the table with implicit value for the field, the record got created with the default value. But in the callback insertEnd(), the default value is not updated. In this case I can't have the default value for the insertion of audit table.

Am I wrong or can anyone help? Thanks.

Best Regards,
Nicholas

Lukas Eder

unread,
Oct 9, 2015, 8:16:16 AM10/9/15
to jooq...@googlegroups.com
Hello Nicholas,

Thank you for your enquiry. You're right, this is currently not possible, as the insert() / update() / store() operations currently only fetch the generated identity value back into the record, not the other values generated by triggers or default expressions. There's a pending feature request for this:

In general, you're probably better off writing a database trigger for your audit log. This will make sure that changes produced by bulk inserts / updates will also be reflected in your audit log. You probably cannot guarantee that all insertions will go through Record.insert() and thus through RecordListener.insertEnd().

I hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Nicholas Hu

unread,
Oct 9, 2015, 8:53:06 AM10/9/15
to jOOQ User Group
Hello Lukas,

Alright. Thanks for the quick reply. I'll go for setting default values on application level then. 

Using database triggers was the first choice that came to my instinct. However, due to the number of tables I need to audit and the inconvenience of trigger definition for MySQL (I'd have to define 3 individual triggers each for insert/update/delete operations), letting alone the the risk of incompatibility followed by altering table columns, I choose to do auditing using RecordListeners. There are some drawbacks as you said, but I think the merit of saving huge efforts overcomes those drawbacks though.

Best Regards,
Nicholas

Lukas Eder

unread,
Oct 9, 2015, 9:49:24 AM10/9/15
to jooq...@googlegroups.com
Hi Nicholas,

I see, that makes sense. Yes, other databases have easier ways to deal with these things.

An alternative way to solve this via RecordListeners might be to fetch the record again after insertion, e.g. in insertEnd(). Or you could call "refresh()" on it... Not sure if that fits your application model.

Cheers,
Lukas

Nicholas Hu

unread,
Oct 10, 2015, 1:21:53 AM10/10/15
to jOOQ User Group
Hi Lukas,

Whoa. You are right. The refresh() does work in getting generated values. I put the following code in my insertEnd():

((UpdatableRecord) ctx.record()).refresh();



Many thanks!

Best Regards,
Nicholas

Lukas Eder

unread,
Oct 10, 2015, 3:10:50 AM10/10/15
to jooq...@googlegroups.com
Great, glad it worked out for you :)

Cheers,
Lukas
Reply all
Reply to author
Forward
0 new messages