Skip to content

sql: support REFERENCING clause for CREATE TRIGGER #135655

@DrewKimball

Description

@DrewKimball

This is a tracking issue for the REFERENCING clause for CREATE TRIGGER. The referencing clause allows AFTER triggers to reference the set of modified rows (either before or after the mutation) as a relation, with a custom alias. From the Postgres docs:

The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for an AFTER trigger that is not a constraint trigger; also, if the trigger is an UPDATE trigger, it must not specify a column_name list. OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.

Epic: CRDB-347

Jira issue: CRDB-44665

Metadata

Metadata

Assignees

No one assigned

    Labels

    A-sql-triggerTriggers and Trigger FunctionsC-enhancementSolution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)T-sql-queriesSQL Queries TeamX-anchored-telemetryThe issue number is anchored by telemetry references.docs-known-limitation

    Type

    No type

    Projects

    Status

    Backlog

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions