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: OnlyNEWis available -
UPDATE: BothOLDandNEWare available -
DELETE: OnlyOLDis 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!