New versions: Dev (3.15)

REFERENCING clause

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A trigger is executed while a data mutation is being executed. During this time, it is possible for a trigger to see a row or table's state before (OLD pseudo table) or after (NEW pseudo table) the modification. Specifically:

  • INSERT: Only NEW is available
  • UPDATE: Both OLD and NEW are available
  • DELETE: Only OLD is available

In the rare event when the default OLD or NEW pseudo table identifiers conflict with actual tables in the schema, the REFERENCING clause can be used to rename these identifiers for the scope of a trigger. In some dialects, REFERENCING is always mandatory, and in others, it's not supported at all.

An example would be

 // A trigger that prevents the update of NULL titles in BOOK
create.createTrigger("trg")
      .beforeUpdate().of(BOOK.TITLE)
      .on(BOOK)
      .referencingOldAs("o")
      .referencingNewAs("n")
      .forEachRow()
      .as(if_(BOOK.as("o").TITLE.isNull()).then(
            variable(BOOK.as("n").TITLE.getQualifiedName(), BOOK.TITLE.getDataType()).setNull() 
      ))
      .execute();

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo