cockroachdb / cockroachdb/cockroach
sql: add support for INSTEAD OF triggers
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
This issue tracks adding support for INSTEAD OF triggers to CRDB. INSTEAD OF triggers only operate on views, and completely replace the original mutation operation. They can only be row-level triggers. Generally, an INSTEAD OF trigger maps from a mutation on the view to mutations on the underlying base tables. It is not possible to define a trigger on a materialized view.
Note that CRDB does not currently support direct mutations on views like Postgres does, tracked in https://github.com/cockroachdb/cockroach/issues/20948. This shouldn't be an obstacle to implementing INSTEAD OF triggers, since they entirely replace the mutation anyway.
https://www.postgresql.org/docs/18/trigger-definition.html
> On views, triggers can be defined to execute instead of INSERT, UPDATE, or DELETE operations. Such INSTEAD OF triggers are fired once for each row that needs to be modified in the view. It is the responsibility of the trigger's function to perform the necessary modifications to the view's underlying base table(s) and, where appropriate, return the modified row as it will appear in the view. Triggers on views can also be defined to execute once per SQL statement, before or after INSERT, UPDATE, or DELETE operations. However, such triggers are fired only if there is also an INSTEAD OF trigger on the view. Otherwise, any statement targeting the view must be rewritten into a statement affecting its underlying base table(s), and then the triggers that will be fired are the ones attached to the base table(s).
Epic CRDB-347
Jira issue: CRDB-39863
Contributor guide
Assessment
This issue has not been assessed yet.