cockroachdb / cockroachdb/cockroach

sql: add support for INSTEAD OF triggers

Open
#126,363 0 comments 0 reactions 0 assignees View on GitHub
A-sql-pgcompat A-sql-trigger C-enhancement docs-known-limitation T-sql-foundations X-anchored-telemetry
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.