Add support for { CREATE | DROP } TRIGGER commands

[lukaseder]

Now that we support BEGIN .. END style blocks (#6474), and hopefully soon also other procedural language elements (#6475), we should definitely add support for triggers as well.

Tasks

• Add DDL support
  • ALTER (out of scope, will be implemented separately: Add support for ALTER TRIGGER commands #11322)
  • CREATE
    • OR REPLACE
    • BEFORE, AFTER, INSTEAD OF
    • INSERT, UPDATE [ OF ], DELETE
    • REFERENCING
    • FOR EACH ROW, FOR EACH STATEMENT
    • WHEN
  • DROP
  • INSERTING, UPDATING, DELETING keywords (see Add support for TRIGGER pseudo columns DELETING, INSERTING, UPDATING #11246)
• Parser support
  • ALTER (out of scope, will be implemented separately: Add support for ALTER TRIGGER commands #11322)
  • CREATE
  • DROP
• Dialect support (more dialects here: Add support for { CREATE | DROP } TRIGGER commands in more dialects #11989)
  • Db2
  • Firebird
  • HSQLDB
  • MariaDB
  • MySQL
  • Oracle
    • We must execute these commands as STATIC_STATEMENT in Oracle, to support the :OLD syntax, to prevent Oracle from thinking this is an actual bind variable 🙄
  • SQL Server
• Emulations
  • OR REPLACE
  • WHEN can be emulated simply, by moving the predicate into the trigger body as an IF statement
  • REFERENCING
    • Some dialects don't support default aliases (e.g. Db2). We can auto-generate REFERENCING OLD AS OLD NEW AS NEW in those cases for better standardisation
  • INSERT OR UPDATE OR DELETE Could be emulated by splitting the trigger into separate triggers, but let's not do that for now.
  • INSTEAD OF cannot be emulated
  • UPDATE OF cannote be emulated

Tasks from separate issues:

• Create an org.jooq.Trigger meta model (see Add Trigger runtime meta model #11248)
• Interpret DDL (see Add Trigger runtime meta model #11248)
• More emulations, etc (see Add support for { CREATE | DROP } TRIGGER commands in more dialects #11989)

Out of scope features:

• Further improvements to the CREATE TRIGGER statement (see Add support for additional CREATE TRIGGER features #11321)

References:

• Db2: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000931.html
• MySQL: https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html
• Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/CREATE-TRIGGER-statement.html#GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B
• PostgreSQL: https://www.postgresql.org/docs/current/sql-createtrigger.html
• SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

---

See also:
https://stackoverflow.com/q/47969132/521799

[lukaseder]

This will ship in jOOQ 3.15

[lukaseder]

More dialects will be supported in a future release: #11989