SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER	[dbo].[per_bez_gen_values]
	ON			[dbo].[per_bez]
	FOR INSERT, UPDATE
AS
BEGIN
	SET NOCOUNT ON;

	WITH t1(fk_per,typ,anzahl) AS (
		--Anzahl der bereits vorhandenen, konkurierenden Einträge zu betroffenen FKs
		SELECT	per_bez.fk_per,
				per_bez.typ,
				count(DISTINCT per_bez.pk) AS anzahl
		FROM	per_bez
		INNER JOIN INSERTED i
		ON		per_bez.fk_per = i.fk_per
		GROUP BY per_bez.fk_per,per_bez.typ
		), t2(pk,sortierung) AS (
		SELECT	per_bez.pk,
				ROW_NUMBER() OVER (	PARTITION BY per_bez.fk_per
									ORDER BY	(	CASE
													WHEN	per_bez.typ = 'Anrede'					THEN	1
													WHEN	per_bez.typ = 'Rufname'					THEN	2
													WHEN	per_bez.typ = 'Vorname'					THEN	3
													WHEN	per_bez.typ = 'Spitzname'				THEN	4
													WHEN	per_bez.typ = 'Nachname'				THEN	5
													WHEN	per_bez.typ = 'Geburtsname'				THEN	6
													WHEN	per_bez.typ = 'Handelsregister-Beruf'	THEN	7
													WHEN	per_bez.typ = 'Handelsregister-Ort'		THEN	8
													WHEN	per_bez.typ = 'Mandat'					THEN	9
													ELSE	10
													END ),
												(	CASE
													WHEN	d.pk IS NULL													--Datensatz ist neu (LEFT JOIN DELETED WHERE DELETED ist nicht existent)
													AND		i.pk IS NOT NULL
													THEN	convert(INT,isnull(per_bez.sortierung,1)) * 10 + t1.anzahl * 10	--Sortierung der neuen Datensätze + Anzahl vorhandener Datensätze damit sich neue Datensätze hinten anschließen
													WHEN	d.pk IS NOT NULL												--Datensatz wird geändert (vergleichbar mit INNER JOIN DELETED entspricht allen geänderten Datensätzen)
													AND		i.pk IS NOT NULL
													THEN	convert(INT,isnull(per_bez.sortierung,1)) * 10 - 5				--Datensatz muss den bisher auf der Position befindlichen Datensatz verdrängen, also davor eingeordnet werden
													WHEN	i.pk IS NULL													--Datensatz wurde nicht geändert, muss aber mit sortiert werden
													THEN	convert(INT,isnull(per_bez.sortierung,1)) * 10
													END )) AS sortierung
		FROM	per_bez
		--Alle Datensätze von betroffenen FKs aktuallisieren
		INNER JOIN (	SELECT	fk_per
						FROM	INSERTED
						GROUP BY fk_per ) t2
		ON		per_bez.fk_per = t2.fk_per
		LEFT JOIN INSERTED i
		ON		per_bez.pk = i.pk
		LEFT JOIN DELETED d
		ON		per_bez.pk = d.pk
		LEFT JOIN t1
		ON		per_bez.fk_per = t1.fk_per
		AND		per_bez.typ = t1.typ
		)
	UPDATE	per_bez
	SET		per_bez.sortierung = t2.sortierung,
			per_bez.lasttrigger = 'per_bez_gen_values*a'
	FROM	per_bez
	INNER JOIN t2
	ON		per_bez.pk = t2.pk
	WHERE	isnull(per_bez.sortierung,0) != t2.sortierung;

END;
GO