Trigger und Funktionen

Status: Entwurf · Spec-Kandidat: ja

Funktionen

`mdm.set_updated_at`

Generischer Trigger zur Pflege des updated_at-Feldes.

CREATE OR REPLACE FUNCTION mdm.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

`mdm.entity_before_write`

Spezifischer Trigger für mdm.DDM_ENTITY. Setzt updated_at, erhöht bei UPDATE die Versionsnummer und berechnet den Volltextvektor.

CREATE OR REPLACE FUNCTION mdm.entity_before_write()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    NEW.updated_at := now();
    NEW.version := OLD.version + 1;
  ELSE
    NEW.updated_at := now();
  END IF;

  NEW.search_vector := to_tsvector(
    'simple',
    trim(
      both ' ' from
      coalesce(NEW.code, '') || ' ' ||
      coalesce(NEW.name, '') || ' ' ||
      regexp_replace(coalesce(NEW.attributes::text, ''), '[{}":,]+', ' ', 'g')
    )
  );

  RETURN NEW;
END;
$$;

Hinweise:

version wird ausschließlich durch diesen Trigger gepflegt. Anwendungen dürfen version nicht setzen.
search_vector wird ausschließlich durch diesen Trigger gepflegt. Anwendungen dürfen ihn nicht schreiben.
Der Vektor verwendet die Konfiguration simple (keine Sprach-Stemmer); bei Bedarf kann pro Sprache angepasst werden (offen).

`mdm.validate_entity_relation_types`

Sicherheitsnetz: prüft, dass die Endpunkte einer DDM_ENTITY_RELATION zum deklarierten DDM_RELATION_TYPE passen und beide Endpunkte zum selben Tenant wie die Beziehung gehören. Cross-Tenant-Beziehungen sind unzulässig.

CREATE OR REPLACE FUNCTION mdm.validate_entity_relation_types()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_from_type uuid;
  v_to_type uuid;
  v_from_tenant uuid;
  v_to_tenant uuid;
  v_expected_from_type uuid;
  v_expected_to_type uuid;
BEGIN
  SELECT e.entity_type_id, e.tenant_id INTO v_from_type, v_from_tenant
  FROM mdm.DDM_ENTITY e WHERE e.id = NEW.from_entity_id;

  SELECT e.entity_type_id, e.tenant_id INTO v_to_type, v_to_tenant
  FROM mdm.DDM_ENTITY e WHERE e.id = NEW.to_entity_id;

  SELECT rt.from_entity_type_id, rt.to_entity_type_id
    INTO v_expected_from_type, v_expected_to_type
  FROM mdm.DDM_RELATION_TYPE rt WHERE rt.id = NEW.relation_type_id;

  IF v_from_type IS NULL OR v_to_type IS NULL THEN
    RAISE EXCEPTION 'Relation enthält unbekannte Entitäten';
  END IF;

  IF v_expected_from_type IS DISTINCT FROM v_from_type THEN
    RAISE EXCEPTION
      'Ungültiger from_entity_type für DDM_RELATION_TYPE %', NEW.relation_type_id;
  END IF;

  IF v_expected_to_type IS DISTINCT FROM v_to_type THEN
    RAISE EXCEPTION
      'Ungültiger to_entity_type für DDM_RELATION_TYPE %', NEW.relation_type_id;
  END IF;

  -- Cross-Tenant-Beziehungen sind verboten.
  IF NEW.tenant_id IS DISTINCT FROM v_from_tenant
     OR NEW.tenant_id IS DISTINCT FROM v_to_tenant THEN
    RAISE EXCEPTION
      'Tenant-Inkonsistenz: relation AA_TENANT=% from-AA_TENANT=% to-AA_TENANT=%',
      NEW.tenant_id, v_from_tenant, v_to_tenant;
  END IF;

  RETURN NEW;
END;
$$;

`mdm.validate_attachment_tenant`

Sicherheitsnetz für DDM_ATTACHMENT: erzwingt Tenant-Konsistenz attachment.tenant_id = entity.tenant_id, falls entity_id gesetzt ist. Entkoppelte Anhänge (entity_id IS NULL) bestehen ausschließlich auf eigener tenant_id.

CREATE OR REPLACE FUNCTION mdm.validate_attachment_tenant()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_entity_tenant uuid;
BEGIN
  IF NEW.entity_id IS NULL THEN
    RETURN NEW;
  END IF;

  SELECT e.tenant_id INTO v_entity_tenant
  FROM mdm.DDM_ENTITY e WHERE e.id = NEW.entity_id;

  IF v_entity_tenant IS NULL THEN
    RAISE EXCEPTION 'Anhang referenziert unbekannte Entität %', NEW.entity_id;
  END IF;

  IF NEW.tenant_id IS DISTINCT FROM v_entity_tenant THEN
    RAISE EXCEPTION
      'Tenant-Inkonsistenz: attachment AA_TENANT=% entity-AA_TENANT=%',
      NEW.tenant_id, v_entity_tenant;
  END IF;

  RETURN NEW;
END;
$$;

`mdm.validate_external_id_tenant`

Sicherheitsnetz für DDM_ENTITY_EXTERNAL_ID: erzwingt, dass tenant_id der Mapping-Zeile mit tenant_id der referenzierten Entität und des referenzierten Quellsystems übereinstimmt. Ohne diesen Trigger könnte ein versehentlicher Tenant-Mismatch zu Cross-Tenant-Zuordnungen führen.

CREATE OR REPLACE FUNCTION mdm.validate_external_id_tenant()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_entity_tenant uuid;
  v_source_tenant uuid;
BEGIN
  SELECT e.tenant_id INTO v_entity_tenant
  FROM mdm.DDM_ENTITY e WHERE e.id = NEW.entity_id;

  SELECT s.tenant_id INTO v_source_tenant
  FROM mdm.DDM_SOURCE_SYSTEM s WHERE s.id = NEW.source_system_id;

  IF v_entity_tenant IS NULL OR v_source_tenant IS NULL THEN
    RAISE EXCEPTION
      'External-ID-Mapping referenziert unbekannte Entität oder unbekanntes Quellsystem';
  END IF;

  IF NEW.tenant_id IS DISTINCT FROM v_entity_tenant
     OR NEW.tenant_id IS DISTINCT FROM v_source_tenant THEN
    RAISE EXCEPTION
      'Tenant-Inkonsistenz: external_id AA_TENANT=% entity-AA_TENANT=% source-AA_TENANT=%',
      NEW.tenant_id, v_entity_tenant, v_source_tenant;
  END IF;

  RETURN NEW;
END;
$$;

Kardinalität — bewusst kein Trigger

DDM_RELATION_TYPE.min_to_per_from und max_to_per_from werden nicht per Trigger erzwungen (OP-13a, revidiert 2026-04-29).

min_to_per_from: harte Trigger-Erzwingung würde Entity-Anlage blockieren, solange noch keine Beziehung existiert (z. B. customer ohne sales_owner).
max_to_per_from: harte Trigger-Erzwingung blockiert legitime Bulk-Imports und Re-Parenting in Migrations-Jobs.

Beide sind Service-Layer-Regeln (Prüfung beim relate-Aufruf bzw. vor Statusübergang active). Verstöße werden über die Reporting-Views mdm.v_relation_min_violations und mdm.v_relation_max_violations sichtbar gemacht — Stewardship-Inbox, kein Schreib-Block.

Trigger-Anbindungen

-- updated_at auf den Metadaten-Tabellen
CREATE TRIGGER trg_entity_type_set_updated_at
BEFORE UPDATE ON mdm.DDM_ENTITY_TYPE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_enum_set_set_updated_at
BEFORE UPDATE ON mdm.DDM_ENUM_SET
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_enum_value_set_updated_at
BEFORE UPDATE ON mdm.DDM_ENUM_VALUE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_relation_type_set_updated_at
BEFORE UPDATE ON mdm.DDM_RELATION_TYPE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_entity_type_attribute_set_updated_at
BEFORE UPDATE ON mdm.DDM_ENTITY_TYPE_ATTRIBUTE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

-- DDM_ENTITY-spezifischer Write-Trigger (Version + search_vector + updated_at)
CREATE TRIGGER trg_entity_before_write
BEFORE INSERT OR UPDATE OF code, name, attributes, status, metadata
ON mdm.DDM_ENTITY
FOR EACH ROW EXECUTE FUNCTION mdm.entity_before_write();

-- DDM_ENTITY_RELATION
CREATE TRIGGER trg_entity_relation_set_updated_at
BEFORE UPDATE ON mdm.DDM_ENTITY_RELATION
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_validate_entity_relation_types
BEFORE INSERT OR UPDATE OF relation_type_id, from_entity_id, to_entity_id, tenant_id
ON mdm.DDM_ENTITY_RELATION
FOR EACH ROW EXECUTE FUNCTION mdm.validate_entity_relation_types();

-- Kardinalität: kein Trigger (OP-13a). min/max sind Service-Layer-Regeln.

-- Tenant + DDM_RELATION_TYPE_ATTRIBUTE updated_at
CREATE TRIGGER trg_tenant_set_updated_at
BEFORE UPDATE ON mdm.AA_TENANT
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_relation_type_attribute_set_updated_at
BEFORE UPDATE ON mdm.DDM_RELATION_TYPE_ATTRIBUTE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

-- Email-Templates: updated_at
CREATE TRIGGER trg_email_template_set_updated_at
BEFORE UPDATE ON mdm.AA_EMAIL_TEMPLATE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

-- Quellsystem-Registry: updated_at
CREATE TRIGGER trg_source_system_set_updated_at
BEFORE UPDATE ON mdm.DDM_SOURCE_SYSTEM
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

-- External-ID-Mapping: Tenant-Konsistenz erzwingen
CREATE TRIGGER trg_validate_external_id_tenant
BEFORE INSERT OR UPDATE OF tenant_id, entity_id, source_system_id
ON mdm.DDM_ENTITY_EXTERNAL_ID
FOR EACH ROW EXECUTE FUNCTION mdm.validate_external_id_tenant();

-- Attachment: Tenant-Konsistenz erzwingen
CREATE TRIGGER trg_validate_attachment_tenant
BEFORE INSERT OR UPDATE OF tenant_id, entity_id
ON mdm.DDM_ATTACHMENT
FOR EACH ROW EXECUTE FUNCTION mdm.validate_attachment_tenant();

AA_JOB, AA_EMAIL_SUPPRESSION und AA_EMAIL_LOG haben kein updated_at und brauchen daher keinen set_updated_at-Trigger. Job-Status-Änderungen sind explizit Worker-getrieben (siehe Asynchrone Jobs); AA_EMAIL_LOG ist append-only.

RBAC / ACL

`mdm.validate_role_permission_scope`

Prüft, dass die Scope-Felder einer AA_ROLE_PERMISSION-Zeile zur deklarierten AA_PERMISSION.scope passen, und dass referenzierte Scope-Objekte existieren und nicht soft-gelöscht sind.

CREATE OR REPLACE FUNCTION mdm.validate_role_permission_scope()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_scope mdm.acl_scope;
BEGIN
  SELECT scope INTO v_scope
  FROM mdm.AA_PERMISSION WHERE id = NEW.permission_id;

  IF v_scope IS NULL THEN
    RAISE EXCEPTION 'AA_PERMISSION % nicht gefunden', NEW.permission_id;
  END IF;

  IF v_scope = 'global' THEN
    IF NEW.scope_tenant_id IS NOT NULL
       OR NEW.scope_entity_type_id IS NOT NULL
       OR NEW.scope_entity_id IS NOT NULL
       OR NEW.scope_relation_type_id IS NOT NULL THEN
      RAISE EXCEPTION
        'AA_ROLE_PERMISSION: scope=global erlaubt keine scope_*-Felder';
    END IF;
  ELSIF v_scope = 'tenant' THEN
    IF NEW.scope_tenant_id IS NULL THEN
      RAISE EXCEPTION
        'AA_ROLE_PERMISSION: scope=tenant erfordert scope_tenant_id';
    END IF;
  ELSIF v_scope = 'entity_type' THEN
    IF NEW.scope_entity_type_id IS NULL THEN
      RAISE EXCEPTION
        'AA_ROLE_PERMISSION: scope=entity_type erfordert scope_entity_type_id';
    END IF;
  ELSIF v_scope = 'entity' THEN
    IF NEW.scope_entity_id IS NULL THEN
      RAISE EXCEPTION
        'AA_ROLE_PERMISSION: scope=entity erfordert scope_entity_id';
    END IF;
  ELSIF v_scope = 'relation_type' THEN
    IF NEW.scope_relation_type_id IS NULL THEN
      RAISE EXCEPTION
        'AA_ROLE_PERMISSION: scope=relation_type erfordert scope_relation_type_id';
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

`mdm.validate_principal_reference`

Prüft, dass (principal_type, principal_id) auf einen aktiven Eintrag in der jeweils passenden Tabelle (AA_APP_USER, AA_SERVICE_ACCOUNT, AA_PRINCIPAL_GROUP) verweist. Wird von AA_PRINCIPAL_ROLE, AA_PRINCIPAL_GROUP_MEMBER und AA_ACL_ENTRY genutzt.

CREATE OR REPLACE FUNCTION mdm.validate_principal_reference()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_count integer;
  v_type  mdm.principal_type;
  v_id    uuid;
BEGIN
  -- Hinweis: TG_TABLE_NAME liefert den Tabellennamen aus dem Postgres-Katalog
  -- (lowercase), da unquoted Identifier auf Lowercase gefaltet werden.
  IF TG_TABLE_NAME = 'aa_principal_group_member' THEN
    v_type := NEW.member_principal_type;
    v_id   := NEW.member_principal_id;
  ELSE
    v_type := NEW.principal_type;
    v_id   := NEW.principal_id;
  END IF;

  IF v_type = 'user' THEN
    SELECT count(*) INTO v_count
    FROM mdm.AA_APP_USER
    WHERE id = v_id AND deleted_at IS NULL;
  ELSIF v_type = 'service_account' THEN
    SELECT count(*) INTO v_count
    FROM mdm.AA_SERVICE_ACCOUNT
    WHERE id = v_id AND deleted_at IS NULL;
  ELSIF v_type = 'group' THEN
    SELECT count(*) INTO v_count
    FROM mdm.AA_PRINCIPAL_GROUP
    WHERE id = v_id AND deleted_at IS NULL;
  ELSE
    RAISE EXCEPTION 'principal_type % nicht referenzierbar', v_type;
  END IF;

  IF v_count = 0 THEN
    RAISE EXCEPTION 'Principal (%, %) existiert nicht oder ist gelöscht',
      v_type, v_id;
  END IF;

  RETURN NEW;
END;
$$;

Trigger-Anbindungen RBAC/ACL

-- updated_at
CREATE TRIGGER trg_app_user_set_updated_at
BEFORE UPDATE ON mdm.AA_APP_USER
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_service_account_set_updated_at
BEFORE UPDATE ON mdm.AA_SERVICE_ACCOUNT
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_principal_group_set_updated_at
BEFORE UPDATE ON mdm.AA_PRINCIPAL_GROUP
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_role_set_updated_at
BEFORE UPDATE ON mdm.AA_ROLE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_permission_set_updated_at
BEFORE UPDATE ON mdm.AA_PERMISSION
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_role_permission_set_updated_at
BEFORE UPDATE ON mdm.AA_ROLE_PERMISSION
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_principal_role_set_updated_at
BEFORE UPDATE ON mdm.AA_PRINCIPAL_ROLE
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

CREATE TRIGGER trg_acl_entry_set_updated_at
BEFORE UPDATE ON mdm.AA_ACL_ENTRY
FOR EACH ROW EXECUTE FUNCTION mdm.set_updated_at();

-- Scope-Konsistenz AA_ROLE_PERMISSION gegen AA_PERMISSION.scope
CREATE TRIGGER trg_role_permission_validate_scope
BEFORE INSERT OR UPDATE OF permission_id,
                          scope_entity_type_id,
                          scope_entity_id,
                          scope_relation_type_id
ON mdm.AA_ROLE_PERMISSION
FOR EACH ROW EXECUTE FUNCTION mdm.validate_role_permission_scope();

-- Principal-Existenzprüfung
CREATE TRIGGER trg_principal_role_validate_principal
BEFORE INSERT OR UPDATE OF principal_type, principal_id
ON mdm.AA_PRINCIPAL_ROLE
FOR EACH ROW EXECUTE FUNCTION mdm.validate_principal_reference();

CREATE TRIGGER trg_principal_group_member_validate_principal
BEFORE INSERT OR UPDATE OF member_principal_type, member_principal_id
ON mdm.AA_PRINCIPAL_GROUP_MEMBER
FOR EACH ROW EXECUTE FUNCTION mdm.validate_principal_reference();

CREATE TRIGGER trg_acl_entry_validate_principal
BEFORE INSERT OR UPDATE OF principal_type, principal_id
ON mdm.AA_ACL_ENTRY
FOR EACH ROW EXECUTE FUNCTION mdm.validate_principal_reference();

Hinweise RBAC/ACL

validate_role_permission_scope und validate_principal_reference sind Sicherheitsnetze. Der Service-Layer muss diese Konsistenz vorher prüfen, damit Anwender saubere Fehler bekommen.
AA_PERMISSION-Zeilen werden durch System-Migrationen verwaltet (is_system=true); Service darf sie nicht löschen.
Effektive Rechte werden über mdm.v_effective_permission (siehe Views) berechnet — nicht durch ad-hoc SQL im Service.

Hinweise zur Anwendungsentwicklung

Anwendungen müssen Trigger-Exceptions als fachliche Fehler an den Aufrufer durchreichen, nicht stillschweigend abfangen.
entity_before_write wird nur ausgelöst bei UPDATE auf code, name, attributes, status, metadata. Pure technische Updates (z. B. nur archived_at setzen) lösen ihn nicht aus – das ist gewollt, damit Archivierung den Volltextindex nicht neu berechnet, und damit version nicht für rein technische Felder hochzählt.
Soft Delete (deleted_at) wird ohne Trigger gepflegt; der Service-Layer muss AA_AUDIT_LOG und ggf. DDM_ENTITY_VERSION selbst schreiben.

Offen

Sprache des to_tsvector (simple vs. german/english) – für Volltextsuche relevant.
Generische Audit-Trigger auf Tabellenebene wären eine Alternative zur Service-Layer-Audit-Logik (siehe Audit) und sind explizit nicht vorgesehen, sollen aber dokumentiert offen bleiben.

Trigger und Funktionen

Funktionen

mdm.set_updated_at

mdm.entity_before_write

mdm.validate_entity_relation_types

mdm.validate_attachment_tenant

mdm.validate_external_id_tenant